CODEWITHSUNDEEP
sql-server-2008constraints
user2067567
user2067567

Reputation: 3803

Drop All constraints in a Table

Am trying to write script for removing Constraints.

I have the below function to select the Constarints in my DataBase

SELECT  name
    FROM sys.foreign_keys

And I have written alter scripts using the above scripts

SELECT 
    'ALTER TABLE ' + OBJECT_NAME(parent_object_id) + 
    ' DROP CONSTRAINT ' + name
FROM sys.foreign_keys

Using the above query how can I execute these constraints ?

I can use DROP DATABASE DBName. But am just trying to drop tables by dropping Constraints.

is it possible without going for SP ? Or any easy ways I can proceed?

Upvotes: 31

Views: 97490

Answers (3)

Alexandre Piva
Alexandre Piva

Reputation: 99

The correct-marked question does not work for me. But this works for me in SQL Server 2017:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))
    + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) +
    ' DROP CONSTRAINT ' + QUOTENAME(name) + ';'
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
    AND OBJECT_NAME(PARENT_OBJECT_ID) LIKE 'your_table_name';

EXEC sp_executesql @sql;

Upvotes: 7

BrownsFan
BrownsFan

Reputation: 329

This worked for me in SQL Server 2008:

DECLARE @SQL NVARCHAR(MAX) = N'';

SELECT @SQL += N'
ALTER TABLE ' + OBJECT_NAME(PARENT_OBJECT_ID) + ' DROP CONSTRAINT ' + OBJECT_NAME(OBJECT_ID) + ';' 
FROM SYS.OBJECTS
WHERE TYPE_DESC LIKE '%CONSTRAINT' AND OBJECT_NAME(PARENT_OBJECT_ID) = 'YOUR_TABLE';

PRINT @SQL
--EXECUTE(@SQL)

Of course, uncomment the EXECUTE(@SQL) when ready to run

Upvotes: 32

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Well you can always copy the output from the bottom pane, paste it into the top pane, and hit F5. Or you can build a string to execute directly:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))
    + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + 
    ' DROP CONSTRAINT ' + QUOTENAME(name) + ';'
FROM sys.foreign_keys;

PRINT @sql;
-- EXEC sp_executesql @sql;

(When you are happy with the PRINT output, comment it out and uncomment the EXEC. Note that the print output will be truncated to 8K in Management Studio but the variable really holds the entire command.)

Also I don't know how this really relates to whether you are using a stored procedure or not, or why you are trying to do it "w/o going for SP"... this query can be run as a stored procedure or not, it all depends on how often you're going to call it, where the procedure lives, etc.

Upvotes: 52

Related Questions