Get a list of dependent tables, SQL Server 2005

Question

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

now they say that the primary key foo is the foreign key fee1 and fee2.

Given the name "foo", how do I know that fee1 and fee2 have foriegn dependencies on foo.

Please help, psuedo request will be helpful.

However, I know how to determine, given the name foo, how to get the dependent dependencies of foriegn on foo. using INFORMATION_SCHEMA.USAGE_KEY_COLUMN.

Answer 1

The answer is listed in this url. Very useful tutorial. Great request.

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

Answer 2

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')

, , YourTableNameHere.

Answer 3

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 

,

`WHERE PK.TABLE_Name = "YourTableName"`

Answer 4

, AND TC.TABLE_NAME = 'foo' WHERE

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

Answer 5

I use sp_help to provide a summary level view for different database objects. When you specify a database table, it returns information about primary keys, restrictions, indexes, foreign keys and what they refer to, and which other tables have foreign keys to the specified one. Quickly and easily remember how to get this information right away.