M. FAISOL RIZA

I face condition where I have to find table with certain foreign key name. It is condition when I have to recreate (drop first) the table for rearrange table column. So how to find tables with certain foreign key name?

Here is the script

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
WHERE f.Name LIKE '%ForeignKeyName%'

So that is the way how we find tables with certain foreign key.

Thanks.

Reference : http://blog.sqlauthority.com/2007/09/04/sql-server-2005-find-tables-with-foreign-key-constraint-in-database/