Created
February 7, 2018 07:52
-
-
Save jkljajic/84491e1f3edc911dcc49a3d350208f78 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| ;WITH FKData AS | |
| ( | |
| SELECT | |
| fk.parent_object_id, | |
| fkc.parent_column_id, | |
| fk.referenced_object_id, | |
| fkc.referenced_column_id, | |
| FKCount = COUNT(*) | |
| FROM | |
| sys.foreign_keys fk | |
| INNER JOIN | |
| sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id | |
| GROUP BY | |
| fk.parent_object_id, fkc.parent_column_id, fk.referenced_object_id, fkc.referenced_column_id | |
| HAVING | |
| COUNT(*) > 1 | |
| ), | |
| DuplicateFK AS | |
| ( | |
| SELECT | |
| ROW_NUMBER() OVER(Partition by s1.Name,t1.Name,c1.Name,t2.Name,c2.Name ORDER BY s1.Name) AS RowNumber, | |
| FKName = fk.Name, | |
| ParentSchema = s1.Name, | |
| ParentTable = t1.Name, | |
| ParentColumn = c1.Name, | |
| ReferencedTable = t2.Name, | |
| ReferencedColumn = c2.Name | |
| FROM | |
| sys.foreign_keys fk | |
| INNER JOIN | |
| sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id | |
| INNER JOIN | |
| FKData f ON fk.parent_object_id = f.parent_object_id | |
| AND fk.referenced_object_id = f.referenced_object_id | |
| AND fkc.parent_column_id = f.parent_column_id | |
| AND fkc.referenced_column_id = f.referenced_column_id | |
| INNER JOIN | |
| sys.tables t1 ON f.parent_object_id = t1.object_id | |
| INNER JOIN | |
| sys.columns c1 ON f.parent_object_id = c1.object_id AND f.parent_column_id = c1.column_id | |
| INNER JOIN | |
| sys.schemas s1 ON t1.schema_id = s1.schema_id | |
| INNER JOIN | |
| sys.tables t2 ON f.referenced_object_id = t2.object_id | |
| INNER JOIN | |
| sys.columns c2 ON f.referenced_object_id = c2.object_id AND f.referenced_column_id = c2.column_id | |
| ) | |
| SELECT | |
| RowNumber, | |
| FKName, | |
| ParentSchema, ParentTable, ParentColumn, | |
| ReferencedTable, ReferencedColumn, | |
| DropStmt = 'ALTER TABLE ' + ParentSchema + '.' + ParentTable + | |
| ' DROP CONSTRAINT ' + FKName | |
| FROM | |
| DuplicateFK where RowNumber>1 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment