CODEWITHSUNDEEP
sqlsql-server
YCN
YCN

Reputation: 677

Get list of dependent Tables, SQL Server 2005

lets say I have table1 = 'foo' and 4 other tables fee1, fee2, fee3, fee4

now say the primary key of foo is a foreign key of fee1 and fee2.

Given the name 'foo' how will I get to know that fee1 and fee2 has foriegn key dependencies on foo.

Please help, a psuedo query would be helpful.

However, I know how to figure out given the name foo how to get the foriegn key dependencies of foo alone. using INFORMATION_SCHEMA.USAGE_KEY_COLUMN.

Upvotes: 3

Views: 4143

Answers (5)

YCN
YCN

Reputation: 677

The answer is in this URL. A very helpful tutorial.. Great query.

http://www.jasinskionline.com/technicalwiki/Default.aspx?Page=List-Tables-in-Dependency-Order-SQL-Server&NS=&AspxAutoDetectCookieSupport=1

Upvotes: 0

Brian Gideon
Brian Gideon

Reputation: 48949

I use sp_help for providing a summary level view of the different database objects. When you specify a database table it returns information regarding primary keys, constraints, indexes, foreign keys and what they reference, and which other tables have foreign keys to the one specified. It is quick and easy to remember way of getting this information all at once.

Upvotes: 0

Raj More
Raj More

Reputation: 48016

Try this

SELECT 
    'CONSTRAINT :' + C.Constraint_Name + '
    Goes FROM ' + PK.TABLE_NAME + '.' + PT.COLUMN_NAME + '
    To ' + FK.TABLE_NAME + '.' + CU.COLUMN_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

To get your specific table, add

`WHERE PK.TABLE_Name = "YourTableName"`

Upvotes: 1

marc_s
marc_s

Reputation: 754518

Try this in SQL Server Mgmt Studio:

SELECT  
    fk.name,
    OBJECT_NAME(fk.parent_object_id) 'Child table'
FROM 
    sys.foreign_keys fk
WHERE
    fk.referenced_object_id = OBJECT_ID('YourTableNameHEre')

This will list all the foreign key constraints and what table they're coming from that are referencing your YourTableNameHere table.

Upvotes: 7

SQLMenace
SQLMenace

Reputation: 135011

one way, just add AND TC.TABLE_NAME = 'foo' to the WHERE clause

SELECT TC.CONSTRAINT_SCHEMA + '.'+ TC.TABLE_NAME AS PRIMARYKEYTABLE
,TC.CONSTRAINT_NAME AS PRIMARYKEY
,COALESCE(RC1.CONSTRAINT_NAME,'N/A') AS FOREIGNKEY
,CASE WHEN TC2.TABLE_NAME IS NULL THEN 'N/A'
 ELSE TC.CONSTRAINT_SCHEMA + '.' + TC2.TABLE_NAME  END AS FOREIGNKEYTABLE
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
 LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC1 
        ON TC.CONSTRAINT_NAME =RC1.UNIQUE_CONSTRAINT_NAME
 LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC2 
        ON TC2.CONSTRAINT_NAME =RC1.CONSTRAINT_NAME
 WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'
 ORDER BY TC.TABLE_NAME,TC.CONSTRAINT_NAME,RC1.CONSTRAINT_NAME

From Find all Primary and Foreign Keys In A Database

Upvotes: 0

Related Questions