SQL Server - Get Table Dependencies via INFORMATION_SCHEMA - Template for SSMS

The following is a SQL Server script used to get a table's dependencies. The script uses SSMS template syntax, so you can add it to the SSMS Template Explorer and use the (Ctrl-Shift-M) keyboard sequence to set the script's parameters. The script returns the following for each dependency:

• Parent Table Schema Name
• Parent Table Name
• Parent Table Primary Key Field Name
• Foreign Table Schema Name
• Foreign Table Name
• Foreign Table Foreign Key Field Name
• Foreign Table Foreign Key Constraint Name

What's nice is that the script uses INFORMATION_SCHEMA, so it may be used with other databases that support INFORMATION_SCHEMA; however, I have not tested the script with other databases. If you give the script a run on another database, I'd love to here about your experience.

-- ======================================================================
-- Template used to determine a specific table's dependencies.
--
-- Use the Specify Values for Template Parameters command (Ctrl-Shift-M) 
-- to fill in the parameter values below.
-- @schema_name:  the schema name of the table to retrieve dependencies
-- @table_name:  the table name of the table to retrieve dependencies
-- ======================================================================

DECLARE @SchemaName VARCHAR(128), @TableName VARCHAR(128);
SELECT @SchemaName = N'<schema_name, VARCHAR(128), dbo>', @TableName = N'<table_name, VARCHAR(128),>'

SELECT DISTINCT  
       pt.TABLE_SCHEMA AS ParentSchema, 
       pt.TABLE_NAME AS ParentTable, 
       pt.COLUMN_NAME AS ParentPrimaryField, 
       fk.TABLE_SCHEMA AS ForeignSchema, 
       fk.TABLE_NAME AS ForeignTable, 
       cu.COLUMN_NAME AS ForeignKeyField, 
       c.CONSTRAINT_NAME AS ForeignKeyConstraint 
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 tc.TABLE_SCHEMA, 
                       tc.TABLE_NAME, 
                       kcu.COLUMN_NAME 
                FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
                       INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
                            ON  tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME 
                WHERE  (tc.CONSTRAINT_TYPE = 'PRIMARY KEY') 
            ) pt 
            ON  pt.TABLE_NAME = pk.TABLE_NAME 
WHERE  (pk.TABLE_NAME = @TableName) 
       AND (pk.TABLE_SCHEMA = @SchemaName) 
ORDER BY 
       ForeignTable ASC; 

Thanks for reading...