Find Tables With Foreign Key Constraint in Database ~ Don't Call Us - We'll Call You

Saturday, May 2, 2009

Find Tables With Foreign Key Constraint in Database

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

/*** Script all Foreign Key Constraints ***/
/*** The Result Set can be used to copy constraints to your testing DB or to keep on hand in case of errors. ***/
SELECT
‘ALTER TABLE ‘+FK.TABLE_NAME+
‘ ADD CONSTRAINT ‘+C.CONSTRAINT_NAME+’ FOREIGN KEY’+
‘(’+CU.COLUMN_NAME+’) ‘+
‘REFERENCES ‘+PK.TABLE_NAME+
‘(’+PT.COLUMN_NAME+’)’ ForeignKeyScripts
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = ‘PRIMARY KEY’
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
–WHERE PK.TABLE_NAME IN (’Table1′, ‘Table2′)
–WHERE FK.TABLE_NAME IN (’Table1′, ‘Table2′)


No comments:

Post a Comment