This article is a finding foreign key in tables. Hope that you may not waste your time with finding in manually. Let’s try to get the column name dependencies with sys.sql_dependencies.

See all stored procedure or Function dependencies results from tables, stored procedures and views by using below statement. You may enhance filtering logic and expend the query if you wish to.

1-      This is an example to get all stored procedures and functions name from tables, stored procedures and views.

SELECT

TOP (100)PERCENTOBJECT_NAME(d.object_id)AS SP_Or_Function,

OBJECT_NAME(d.referenced_major_id)AS TableReferenced

FROM sys.sql_dependencies AS d

INNER JOIN sys.all_sql_modules AS m ON m.object_id= d.object_id

GROUP BY OBJECT_NAME(d.object_id), OBJECT_NAME(d.referenced_major_id)

ORDER BY SP_Or_Function, TableReferenced

2-      Let’s find foreign key dependencies in tables. The following statement is finding all the primary 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_keysAS f

INNER JOIN sys.foreign_key_columnsAS fc ON f.OBJECT_ID= fc.constraint_object_id

3-      This is also finding all the primary key constraint in database base on your filtering object name.

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 COL_NAME(fc.parent_object_id, fc.parent_column_id)=‘ObjectName’

4-      The last example is to get dependencies info from sys.syscomments. Please run following statement and see the result for your reference.

SELECT DISTINCT so.name

FROM sys.syscomments sc

INNER JOIN sys.objects so on sc.id=so.object_id

WHERE sc.textLIKE‘%ObjectName%’

Note: This may help you when you need to update or increase to one of your column’s max characters in tables or other related objects. It is easy and useful. Have a nice day.