Wednesday, 15 July 2015

SQL: How to Identify Foreign Key Constraints of a Certain Table

SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,
      OBJECT_NAME(constraint_object_id) as ConstraintName,
      OBJECT_NAME(parent_object_id) as TableName,
       clm1.name as ColumnName,      
OBJECT_NAME(referenced_object_id) as ReferencedTableName,
       clm2.name as ReferencedColumnName
FROM sys.foreign_key_columns fk
      JOIN sys.columns clm1      
ON fk.parent_column_id = clm1.column_id
           AND fk.parent_object_id = clm1.object_id
      JOIN sys.columns clm2      
ON fk.referenced_column_id = clm2.column_id
           AND fk.referenced_object_id= clm2.object_id
--WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not want to be truncated')
WHERE OBJECT_NAME(referenced_object_id) = 'document'
ORDER BY OBJECT_NAME(parent_object_id)

No comments:

Post a Comment