How to find SQL Relationship and Dependencies (Dependency)

 
 
SQL SERVER – Query to display Foreign Key relationships and name of the constraint for each table in Database.
I tested the query at the following website and it seems to work.  In the response section someone claims to have modified the query and made it faster.  A where can be added to it easily to limit it to a specific table.
 
This is a cool system Stored Procedrue, but I believe it’s not documented.
EXEC sp_MSdependencies N’mo_company’, null, 1315327
 
System Stored Procedure that is documented, but doesn’t give you what you really want. But kinda cool.
EXEC sp_depends @objname = N’tablename’
 
*****************************************************************************************
EXEC sp_depends @objname = N’tablename’
 
EXEC sp_MSdependencies N’tablename’, null, 1315327
*****************************************************************************************
SQL SERVER – Query to display Foreign Key relationships and name of the constraint for each table in Database.
This is very long query. Optionally, we can limit the query to return results for one or more than one table.

SELECT
     K_Table  = FK.TABLE_NAME,
     FK_Column = CU.COLUMN_NAME,
     PK_Table  = PK.TABLE_NAME,
     PK_Column = PT.COLUMN_NAME,
     Constraint_Name = C.CONSTRAINT_NAME
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
—- optional:
ORDER BY
     1,2,3,4
WHERE      PK.TABLE_NAME=’something’WHERE      FK.TABLE_NAME=’something’

WHERE      PK.TABLE_NAME IN (‘one_thing’, ‘another’)

WHERE      FK.TABLE_NAME IN (‘one_thing’, ‘another’)

*****************************************************************************************

SELECT
CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME,
TABLE_CATALOG = FK.TABLE_CATALOG,
TABLE_SCHEMA = FK.TABLE_SCHEMA,
TABLE_NAME = FK.TABLE_NAME,
COLUMN_NAME = FK_COLS.COLUMN_NAME,
REFERENCED_TABLE_CATALOG = PK.TABLE_CATALOG,
REFERENCED_TABLE_SCHEMA = PK.TABLE_SCHEMA,
REFERENCED_TABLE_NAME = PK.TABLE_NAME,
REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
AND FK.CONSTRAINT_TYPE = ‘FOREIGN KEY’
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
AND PK.CONSTRAINT_TYPE = ‘PRIMARY KEY’
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME


Leave a Reply

Your email address will not be published. Required fields are marked *