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?

回答1:

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.

回答2:

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

回答3:

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;