A couple of month ago I was working on a replication project that lead me to the decision to remove all self referenced relationships. But i was dealing with a bigger database like more than 400 tables. so i wrote the following script to fulfill the task
| SELECT DISTINCT * FROM ( SELECT fk.Constraint_name AS FKConstaint, fk.table_schema AS FKSchema, fk.table_name AS FKTable, fkCol.COLUMN_NAME AS FKColumn, pk.Constraint_name AS PKConstaint, pk.table_schema AS PKSchema, pk.table_name AS PKTable, pkCol.COLUMN_NAME AS PKColumn FROM Information_schema.TABLE_CONSTRAINTS fk INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ref ON fk.CONSTRAINT_NAME = ref.CONSTRAINT_NAME INNER JOIN Information_schema.TABLE_CONSTRAINTS pk ON pk.CONSTRAINT_NAME = ref.UNIQUE_CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE fkCol ON fk.CONSTRAINT_NAME = fkCol.Constraint_name INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkCol ON pk.CONSTRAINT_NAME = pkCol.Constraint_name WHERE fk.CONSTRAINT_type = 'FOREIGN KEY' ) AS REFTABLE WHERE fkSchema = pkSchema AND fkTable = pkTable AND PKColumn <> FKColumn |
No comments:
Post a Comment