Reputation: 1562
I'm trying to drop a few constraints which have been automatically generated when I add the default value somewhere.
I use the following script to return me the names of the constraints:
SELECT default_constraints.name FROM sys.all_columns
INNER JOIN sys.tables ON all_columns.object_id = tables.object_id
INNER JOIN sys.schemas ON tables.schema_id = schemas.schema_id
INNER JOIN sys.default_constraints ON all_columns.default_object_id = default_constraints.object_id
WHERE tables.name = 'TrainingType'
AND default_constraints.name like 'DF__TrainingT__Soft__%'
OR default_constraints.name like 'DF__TrainingT__EndUs__%'
OR default_constraints.name like 'DF__TrainingC__Compu__%'
This returns me the following:
| name
---------------------------------
1 | DF__TrainingC__Compu__2058C9F1
2 | DF__TrainingT__EndUs__1559B68C
3 | DF__TrainingT__Softw__05CD5A39
Now I'm trying to drop the constraints with these values, but it doesn't allow me to do DROP CONSTRAINT ( ... )
ALTER TABLE TrainingType
DROP CONSTRAINT (
SELECT default_constraints.name FROM sys.all_columns
INNER JOIN sys.tables ON all_columns.object_id = tables.object_id
INNER JOIN sys.schemas ON tables.schema_id = schemas.schema_id
INNER JOIN sys.default_constraints ON all_columns.default_object_id = default_constraints.object_id
WHERE tables.name = 'TrainingType'
AND default_constraints.name like 'DF__TrainingT__Soft__%'
OR default_constraints.name like 'DF__TrainingT__EndUs__%'
OR default_constraints.name like 'DF__TrainingC__Compu__%'
)
So how can I drop the constraints correctly?
Upvotes: 1
Views: 77
Reputation: 38073
Dynamic Sql using the select ... for xml path ('')
method of string concatenation to concatenate the commands into one variable to execute with sp_executesql
:
declare @sql nvarchar(max);
select @sql = (
select
'alter table '+quotename(schema_name(dc.schema_id))
+'.'+quotename(object_name(dc.parent_object_id))
+' drop constraint '+quotename(name)+';'+char(10)
from sys.default_constraints as dc
where parent_object_id = object_id(N'TrainingType')
and dc.name like 'DF__TrainingT__Soft__%'
or dc.name like 'DF__TrainingT__EndUs__%'
or dc.name like 'DF__TrainingC__Compu__%'
for xml path (''), type).value('.','nvarchar(max)')
set @sql = 'use '+quotename(db_name())+';'+char(10)+@sql;
select @sql
exec sp_executesql @sql;
This is a good primer on dynamic sql:
rextester demo: http://rextester.com/HSV25230
Generated code from the demo:
use [rextester];
alter table [dbo].[Pilots] drop constraint [DF__Pilots__df__173EF6DF];
alter table [dbo].[Pilots] drop constraint [DF__Pilots__other_df__18331B18];
Upvotes: 1
Reputation: 15185
You can't inject dynamic qualifiers into a drop clause like that. They have to be issued one at a time. A similar method would be to generate a script to run as individual sql commands.
SELECT
'ALTER TABLE TrainingType DROP CONSTRAINT ( '
+ default_constraints.name +')'
+ CHAR(10)+CHAR(13)+ GO '
FROM sys.all_columns
INNER JOIN sys.tables ON all_columns.object_id = tables.object_id
....
You now have one statement per drop command that you can apply against your database.
Upvotes: 0
Reputation: 27
Maybe it'll work once you add the ID as column and name as a second column.
This should be the proper syntax:
ALTER TABLE "table_name"
DROP [CONSTRAINT|INDEX] "CONSTRAINT_NAME";
Upvotes: 0