CODEWITHSUNDEEP
sqlsql-servermetadatasql-server-2008-r2information-schema
Geshan
Geshan

Reputation: 1141

How do I get dependent tables from sql server information schema (meta data)?

I need to know which columns in which tables are primary key as well as a foreign key, so that I know which tables are dependent (associative/many to many relation) tables. I started with a query like this but its not giving me the thing I need.

  --get all tables with primary keys with pk,fk in it. --not complete
SELECT
TC.TABLE_NAME, TC.CONSTRAINT_TYPE, CCU.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC left JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON TC.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND TC.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND
TC.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON CCU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG AND
CCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA AND CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME

I need to change it so that I can get the table name and column name of the columns which are pk+fk. Help will be appreciated.

Upvotes: 1

Views: 2259

Answers (1)

SQLMenace
SQLMenace

Reputation: 135011

From here: http://wiki.lessthandot.com/index.php/Find_all_Primary_and_Foreign_Keys_In_A_Database

SELECT TC.CONSTRAINT_SCHEMA + '.'+ TC.TABLE_NAME AS PRIMARYKEYTABLE
,TC.CONSTRAINT_NAME AS PRIMARYKEY,
CU.COLUMN_NAME AS [PRIMARY COLUMN Name]
,COALESCE(RC1.CONSTRAINT_NAME,'N/A') AS FOREIGNKEY,
COALESCE(CFU.Column_Name, 'N/A') AS [FOREIGN COLUMN Name]
,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
 INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU ON TC.TABLE_NAME = CU.TABLE_NAME
 and TC.TABLE_SCHEMA = CU.TABLE_SCHEMA and Tc.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
 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
 LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CFU ON RC1.CONSTRAINT_NAME  = CFU.CONSTRAINT_NAME
 WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'
 ORDER BY TC.TABLE_NAME,TC.CONSTRAINT_NAME,RC1.CONSTRAINT_NAME

Upvotes: 2

Related Questions