This looks for PK/UQ constraints with the same columns in the same order in the constraint. It produces the SQL to drop the duplicates.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
WITH cte AS ( SELECT SchemaName = OBJECT_SCHEMA_NAME(skc.parent_object_id), TableName = OBJECT_NAME(skc.parent_object_id), ConstraintName = skc.name, skc.type, [ColumnName(s)] = ca.name FROM sys.key_constraints skc -- make a comma-delimited list of the columns in this constraint, -- column names are in proper order. CROSS APPLY (SELECT name = STUFF((SELECT ',' + sc.name FROM sys.columns sc JOIN sys.index_columns sic ON sc.object_id = sic.object_id AND sc.column_id = sic.column_id WHERE sc.object_id = skc.parent_object_id AND sic.index_id = skc.unique_index_id ORDER BY sic.index_column_id FOR XML PATH(''), TYPE).value('.','varchar(max)') ,1,1,'')) ca ), cte2 AS ( SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY SchemaName, TableName, [ColumnName(s)] ORDER BY type) -- so that PKs are kept over UQs FROM cte ) SELECT SchemaName, TableName, ConstraintName, [ColumnName(s)], SQLCmd = 'ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' DROP CONSTRAINT ' + QUOTENAME(ConstraintName) + ';' FROM cte2 WHERE RN > 1 ORDER BY SchemaName, TableName; |
