Monday, September 14, 2015

Drop all Foreign Key constraints in all tables

DECLARE @sql nvarchar(MAX)
SET @sql = ''

SELECT @sql = @sql + 'ALTER TABLE ' + QUOTENAME(RC.CONSTRAINT_SCHEMA)
    + '.' + QUOTENAME(KCU1.TABLE_NAME)
    + ' DROP CONSTRAINT ' + QUOTENAME(rc.CONSTRAINT_NAME) + '; '
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1
    ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
    AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
    AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME


-- PRINT @sql
EXECUTE(@sql)

0 comments:

Post a Comment