Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Find all Primary and Foreign Keys In A Database
From Wiki
To find all your foreign and primary keys in your database run the code below. The output will return the primary key, primary key table, foreign key, foreign key table. Primary keys that don't have foreign keys will have N/A in the foreign key output
- SELECT t.table_schema AS PrimarySchemaName ,
- t.TABLE_NAME AS PrimaryKeyTable,
- tc.CONSTRAINT_NAME AS PrimaryKey,
- COALESCE(tc2.constraint_schema,'N/A') AS ForeignSchemaName,
- COALESCE(rc1.CONSTRAINT_NAME,'N/A') AS ForeignKey ,
- COALESCE(tc2.TABLE_NAME,'N/A') AS ForeignKeyTable
- FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
- INNER JOIN INFORMATION_SCHEMA.TABLES t ON tc.TABLE_NAME = t.TABLE_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
- WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'
- ORDER BY tc.TABLE_NAME,tc.CONSTRAINT_NAME,rc1.CONSTRAINT_NAME
Here is another way, this one was emailed to us by one of our readers..thank you
- 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
Contributed by: --SQLDenis 20:44, 29 May 2008 (GMT)
Part of SQL Server Programming Hacks
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.