DECLARE @TableName nvarchar(128), @ConstraintName nvarchar(128), @UpdateRule nvarchar(128), @DeleteRule nvarchar(128)
CREATE TABLE #constraints
(
TableName nvarchar(128),
ConstraintName nvarchar(128),
UpdateRule nvarchar(128),
DeleteRule nvarchar(128)
)
INSERT INTO #constraints
SELECT
K_Table = FK.TABLE_NAME,
Constraint_Name = C.CONSTRAINT_NAME,
C.UPDATE_RULE,
C.DELETE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
---- optional:
WHERE PK.TABLE_NAME IN ('Portals')
DECLARE @SQLString nvarchar(500);
DECLARE remove_constraint_tables_cursor CURSOR FOR
SELECT TableName, ConstraintName, UpdateRule, DeleteRule from #constraints
OPEN remove_constraint_tables_cursor
FETCH NEXT FROM remove_constraint_tables_cursor
INTO @TableName, @ConstraintName, @UpdateRule, @DeleteRule
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Table Name: ' + @TableName + ' Constraint:' + @ConstraintName
SET @SQLString = N'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @ConstraintName
EXEC sp_executesql @SQLString
FETCH NEXT FROM remove_constraint_tables_cursor
INTO @TableName, @ConstraintName, @UpdateRule, @DeleteRule
END
CLOSE remove_constraint_tables_cursor
DEALLOCATE remove_constraint_tables_cursor
ALTER TABLE dbo.Portals DROP CONSTRAINT PK_Portals
ALTER TABLE dbo.Portals ADD CONSTRAINT
PK_Portals PRIMARY KEY CLUSTERED
(
PortalID
)
DECLARE add_constraint_tables_cursor CURSOR FOR
SELECT TableName, ConstraintName, UpdateRule, DeleteRule from #constraints
OPEN add_constraint_tables_cursor
FETCH NEXT FROM add_constraint_tables_cursor
INTO @TableName, @ConstraintName, @UpdateRule, @DeleteRule
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = N'ALTER TABLE ' + @TableName + ' ADD CONSTRAINT ' + @ConstraintName
+ ' FOREIGN KEY (PortalID) REFERENCES dbo.Portals (PortalID) ON UPDATE ' + @UpdateRule + ' ON DELETE ' + @DeleteRule
PRINT @SQLString
EXEC sp_executesql @SQLString
FETCH NEXT FROM add_constraint_tables_cursor
INTO @TableName, @ConstraintName, @UpdateRule, @DeleteRule
END
CLOSE add_constraint_tables_cursor
DEALLOCATE add_constraint_tables_cursor