Drop all constraints

Question

I have a simple script, which display all constraints of some table:

SELECT *
FROM DB_NAME.INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'TestTable'

How I can drop all constraints in TestTable and use selection of previous query (MS SQL Server 2012)? Or it will be bad way to drop constraints?

I found some solutions, but I don't understand where they get information about constraints.

Possible dublicate of [Drop All constraints in a Table](http://stackoverflow.com/q/15785405/4275342) — Roman Marusyk, Sep 13 '15 at 13:19

score 3 · Accepted Answer · answered Sep 13 '15 at 13:17

3

Try to build a string with script like:

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

SELECT @sql += N'
ALTER TABLE ' + TABLE_NAME +
    ' DROP CONSTRAINT ' + CONSTRAINT_NAME + ';'
DB_NAME.INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'TestTable';

EXEC sp_executesql @sql;

answered Sep 13 '15 at 13:17

Roman Marusyk

23,328
24
73
116

1

I prefer this one over the other option linked here because Occam's razor. – theFish Sep 13 '22 at 13:36

Drop all constraints

1 Answers1