Loading

Friday, October 16, 2009

Identify Self Referenced Relationships T-SQL





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