Drop All constraints in a Table

Question

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?

Check the comment in this [article][1] . [1]: http://stackoverflow.com/a/6130023/2232256 — dferidarov, Apr 03 '13 at 11:08

score 50 · Accepted Answer · answered Apr 03 '13 at 11:37

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.

Thanks @Aaron. that is Exactly what i was looking for. Ya i ll convert to SP as you said — user2067567, Apr 03 '13 at 12:20
Yes it is essentially string concatenation over every row in the result. You could also say `SELECT @sql = @sql + N'...` in fact that is how you had to do it in SQL Server 2005. This is newer syntax introduced in SQL Server 2008. — Aaron Bertrand, Apr 03 '13 at 14:07

score 31 · Answer 2 · answered Apr 07 '14 at 13:58

31

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

answered Apr 07 '14 at 13:58

BrownsFan

319
3
2

I prefer this one because it shows you how to target specific tables if that's your goal. – AS7K Feb 21 '18 at 22:24
3

There is one mistake in this statement. If constraint name will have dots in its name then execution will fail. You must wrap constraint name in square brackets: `DECLARE @SQL NVARCHAR(MAX) = ''; 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)` Also if you don't want to drop primary key then add one more condition to the WHERE clause: `AND [type] != 'PK'` – Viktors Telle Jul 13 '18 at 08:35
1

Thanks @ViktorsTelle. I had an issue since my constraint contains dots. Solved by your answer. – Aravin Jan 05 '21 at 13:02

Alexandre Piva · Answer 3 · 2018-02-25T13:11:59.620

6

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;

edited Feb 25 '18 at 13:11

answered Feb 12 '18 at 19:23

Alexandre Piva

91
1
4

3

While this code-only post might answer the question, please add an explanation of why it does so. This will help future readers evaluate the answer for their situation. – Tom Brunberg Feb 13 '18 at 07:09
@TomBrunberg Any reason you didn't make the same comment on the other two code-only answers? Are you specifically asking Alexandre for why this fixes what issue he had with the accepted answer? That might be an important distinction for him to keep in mind for why you'd like him to improve this answer. – ruffin Jun 23 '22 at 14:09

Drop All constraints in a Table

3 Answers3

Linked

Related