CODEWITHSUNDEEP
sqlsql-serversql-server-2014
user3680979
user3680979

Reputation: 21

SQL Server 2014 'Incorrect syntax near the keyword 'select'.'

Is there anything wrong in the following sql code:

ALTER TABLE [idconfirm].[EPS_ENROLLMENT] 
DROP CONSTRAINT select d.name from syscolumns c,sysobjects d, sysobjects t 
where c.id=t.id AND d.parent_obj=t.id AND d.type='D' AND t.type='U' 
AND c.name='ENC_TOKEN_KEK_SALT_SIZE' AND t.name='EPS_ENROLLMENT';

Upvotes: 1

Views: 1361

Answers (4)

Steve Pettifer
Steve Pettifer

Reputation: 2043

You really should not be using sysobjects as this has been deprecated for a long time. In fact I'm struggling to understand why no one else has pointed this out yet. Whilst other people are correct in their analysis of the problems with your SQL statement (notably @mrjoltcola who has give a good explanation of why your statement is wrong), as supposed experts answering this question we should also be educating those asking the questions to ensure that they don't use outdated and deprecated syntax. In addition, where there is an opportunity to improve the quality of the OPs code, we should also take it in order to reduce the possibility that at some point they will come back and complain that their code has broken because they upgraded their server or whatever (granted, I know this is about SQL 2014 but the next update will likely cut off sysobjects at the knees as it has had the Sword of Damocles hanging over it long enough).

So, with all that in mind I would adapt mrjoltcola's answer as follows, using sys.columns, sys.tables and sys.default_constraints as well as using explicit INNER JOINS.

SELECT 'ALTER TABLE [idconfirm].[EPS_ENROLLMENT] DROP CONSTRAINT ' + d.[name]
FROM sys.tables t 
INNER JOIN sys.columns c
    ON c.[object_id] = t.[object_id]
INNER JOIN sys.default_constraints d
    ON d.[parent_object_id] = t.[object_id]
    AND d.[parent_column_id] = c.[column_id]

WHERE t.name='EPS_ENROLLMENT'
    AND  c.name='ENC_TOKEN_KEK_SALT_SIZE';

That will give you drop code for that one table and column. Of course, you could assign the result of that query to an NVARCHAR variable and then call sp_executesql if you needed to perform the whole operation programatically like this.

EXEC sp_executesql @sql

However it's all too easy to get into trouble using dynamic SQL so if you're at all unsure then I'd avoid it wherever possible - it should really be a method of last resort.

Upvotes: 1

mrjoltcola
mrjoltcola

Reputation: 20842

You can't just say

 ALTER TABLE FOO DROP CONSTRAINT;

You must give a constraint name:

 ALTER TABLE FOO DROP CONSTRAINT CONS_SOME_CONS_NAME;

It seems you are trying to use the latter query to return a result set of constraint names? You can't feed the ALTER statement with a SELECT like that, it doesn't work either. Either use SQL to generate the script, then save the script and run it, or use dynamic SQL.

Dynamic SQL is one of the most dangerous practices to use when dropping objects, yet I see it suggested so casually on StackOverflow. You don't use it unless you need automation and you need a script that will adapt to potential differences (such as deploying a change script to a customer). Otherwise, you should be practicing safe-DBA and generating the script to a text window, then validate it before executing it. Dynamic SQL jumps directly from the generation to the execution. Take the 15 extra seconds to validate.

You can save it and deploy it to other databases with the knowledge that only the actions in that script will be run. Deploying dynamic SQL also deploys potentially a different action to each database; sometimes this is what you want, and sometimes it is not.

To generate the DDL like so, from the SSMS menu pick "Results to Text", or CTRL-T for short, then run:

SELECT 'ALTER TABLE T DROP CONSTRAINT ' + d.name
  from syscolumns c, sysobjects d, sysobjects t 
    where c.id=t.id AND d.parent_obj=t.id AND d.type='D' AND t.type='U' 
      AND c.name='ENC_TOKEN_KEK_SALT_SIZE' AND t.name='EPS_ENROLLMENT';

Then you have your script, copy-paste it, verify it, save it. This technique works in any database with a data dictionary catalog.

Upvotes: 3

user2864740
user2864740

Reputation: 61865

ALTER TABLE .. DROP CONSTRAINT SELECT .. is not valid syntax; an identifier ("constraint_name") is required after DROP CONSTRAINT.

As with all SQL DDL, the column identifiers are not values and cannot be the result of an expression (SELECT or otherwise). To use a value in such a statement requires building and executing Dynamic SQL.

For instance,

declare @name varchar(200)

-- I recommend not using implicit cross-joins, but ..
select @name=d.name
from syscolumns c, sysobjects d, sysobjects t 
where c.id=t.id AND d.parent_obj=t.id AND d.type='D' AND t.type='U' 
  AND c.name='ENC_TOKEN_KEK_SALT_SIZE' AND t.name='EPS_ENROLLMENT';

-- Build dynamic SQL
SET @sql = 'ALTER TABLE idconfirm.EPS_ENROLLMENT DROP CONSTRAINT ' + @name;
-- And execute it
EXEC (@sql)

Upvotes: 1

Lee
Lee

Reputation: 2992

Do you not need to provide constraint name to drop in your earlier alter statement?

Upvotes: 0

Related Questions