Drop constraint names received from different query

Question

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?

The drop constraint is expecting a static qualifier while you are trying to inject a dynamic one. — Ross Bush
– Ross Bush, Commented Mar 10, 2017 at 14:12
@RossBush I'm fully aware of this, but I'll never know the static qualifier as it will change every day and get a random number at the back — Jordec
– Jordec, Commented Mar 10, 2017 at 14:16

SqlZim · Accepted Answer · 2017-03-10 14:35:08Z

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:

The curse and blessings of dynamic SQL - Erland Sommarskog

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];

I have honestly to god no idea what is happening in that query, but it's working haha
@JorisDecraecker I added a reference for the concatenation using for xml path (''), and removed the stuff(), which I added out of force of habit. Usually there is a delimiter at the beginning of the string that you want removed before the first value being returned.
Alright i'm taking a look into this and it looks interesting. Thanks alot!

fosjo · Accepted Answer · 2017-03-10 14:11:13Z

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";

I'll still only be able to get all these values dynamically and I'll never have the static values (like the syntax is asking now)

Ross Bush · Accepted Answer · 2017-03-10 14:19:49Z

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.

Collectives™ on Stack Overflow

Drop constraint names received from different query

3 Answers 3

3 Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Related