CODEWITHSUNDEEP
sqlsql-serverconstraints
Jordec
Jordec

Reputation: 1562

Drop constraint names received from different query

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

Answers (3)

SqlZim
SqlZim

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

Ross Bush
Ross Bush

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

fosjo
fosjo

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

Related Questions