Drop and Create All Constraints of Database

SELECT 'ALTER TABLE [' + TC.TABLE_SCHEMA + '].[' + TC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+
'DROP CONSTRAINT [' + TC.CONSTRAINT_NAME + ']' + CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
AS [-- Drop Constraints]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FC ON FC.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
WHERE (TC.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE'))
OR (TC.CONSTRAINT_TYPE = 'FOREIGN KEY')
ORDER BY FC.TABLE_NAME DESC,
INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IsClustered') ASC


SELECT 'ALTER TABLE [' + TC.TABLE_SCHEMA + '].[' + TC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+
'ADD CONSTRAINT [' + TC.CONSTRAINT_NAME + ']' + CHAR(13)+CHAR(10)+
'  ' + TC.CONSTRAINT_TYPE +
CASE INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IsClustered')
WHEN 1 THEN ' CLUSTERED'
ELSE ' NONCLUSTERED'
END + CHAR(13)+CHAR(10)+
'  (' +
MAX(CASE KCU.ORDINAL_POSITION WHEN 1 THEN '[' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 2 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 3 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 4 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 5 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 6 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 7 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 8 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 9 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 10 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 11 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 12 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 13 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 14 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 15 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 16 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
')' + CHAR(13)+CHAR(10)+
'WITH FILLFACTOR = ' +
CONVERT(varchar(3), --Replace 0 FILLFACTOR with 100
ISNULL(NULLIF(
INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IndexFillFactor'),
0), 100)
) + CHAR(13)+CHAR(10)+
'ON [' + sfg.groupname + ']' + CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
AS [-- Create PK/UNIQUE Constraints]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
LEFT JOIN sysindexes s ON s.name = TC.CONSTRAINT_NAME
AND s.id = object_id(TC.TABLE_NAME)
LEFT JOIN sysfilegroups sfg ON sfg.groupid = s.groupid
WHERE TC.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE')

GROUP BY
TC.TABLE_SCHEMA,
TC.TABLE_NAME,
TC.CONSTRAINT_NAME,
TC.CONSTRAINT_TYPE,
sfg.groupname
ORDER BY INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IsClustered') DESC

SELECT 'ALTER TABLE [' + FC.TABLE_SCHEMA + '].[' + FC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+
'ADD CONSTRAINT [' + FC.CONSTRAINT_NAME + '] ' + FC.CONSTRAINT_TYPE + CHAR(13)+CHAR(10)+
'  (' +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 1 THEN '[' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 2 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 3 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 4 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 5 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 6 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 7 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 8 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 9 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 10 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 11 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 12 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 13 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 14 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 15 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 16 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
')' + CHAR(13)+CHAR(10)+
'REFERENCES [' + TC.TABLE_SCHEMA + '].[' + TC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+
'  (' +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 1 THEN '[' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 2 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 3 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 4 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 5 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 6 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 7 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 8 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 9 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 10 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 11 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 12 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 13 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 14 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 15 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 16 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
')' + CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
AS [-- Create FK Constraints]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU_PK ON TC.TABLE_NAME = KCU_PK.TABLE_NAME
AND TC.CONSTRAINT_NAME = KCU_PK.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON RC.UNIQUE_CONSTRAINT_NAME = TC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FC ON FC.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU_FK ON FC.TABLE_NAME = KCU_FK.TABLE_NAME
AND FC.CONSTRAINT_NAME = KCU_FK.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE')

GROUP BY
FC.TABLE_SCHEMA,
FC.TABLE_NAME,
FC.CONSTRAINT_NAME,
FC.CONSTRAINT_TYPE,
TC.TABLE_SCHEMA,
TC.TABLE_NAME