Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

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.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Find all Primary and Foreign Keys In A Database

From Wiki

Jump to: navigation, search

Please be aware, that the code bellow is not reliable and should not be used in the production code due to this warning about INFORMATION_SCHEMA.Views (see, for example, http://msdn.microsoft.com/en-us/library/ms181757.aspx): Name of schema that contains the constraint.

Important

Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of a object is to query the sys.objects catalog view.

Therefore it needs to be re-written using sys views, e.g. sys.columns, sys.objects, sys.key_constraints:

See this thread reference http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/732bd071-2c1f-4c23-9215-4ff3822c63c3


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

  1. SELECT t.table_schema AS PrimarySchemaName ,
  2. t.TABLE_NAME AS PrimaryKeyTable,
  3. tc.CONSTRAINT_NAME AS PrimaryKey,
  4. COALESCE(tc2.constraint_schema,'N/A') AS ForeignSchemaName,
  5. COALESCE(rc1.CONSTRAINT_NAME,'N/A') AS ForeignKey ,
  6. COALESCE(tc2.TABLE_NAME,'N/A') AS ForeignKeyTable
  7. FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
  8. INNER JOIN INFORMATION_SCHEMA.TABLES t on tc.TABLE_NAME = t.TABLE_NAME
  9. LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON tc.CONSTRAINT_NAME =rc1.UNIQUE_CONSTRAINT_NAME
  10. LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
  11. WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'
  12. 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

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

Here is the same code as the first version, but with the added column names for PK field and FK field

  1. SELECT TC.CONSTRAINT_SCHEMA + '.'+ TC.TABLE_NAME AS PRIMARYKEYTABLE
  2. ,TC.CONSTRAINT_NAME AS PRIMARYKEY,
  3. CU.COLUMN_NAME as [Primary Column Name]
  4. ,COALESCE(RC1.CONSTRAINT_NAME,'N/A') AS FOREIGNKEY,
  5. coalesce(CFU.Column_Name, 'N/A') as [Foreign Column Name]
  6. ,CASE WHEN TC2.TABLE_NAME IS NULL THEN 'N/A'
  7.  ELSE TC.CONSTRAINT_SCHEMA + '.' + TC2.TABLE_NAME  END AS FOREIGNKEYTABLE
  8.  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
  9.  INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU on TC.TABLE_NAME = CU.TABLE_NAME
  10.  and TC.TABLE_SCHEMA = CU.TABLE_SCHEMA and Tc.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
  11.  LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC1 ON TC.CONSTRAINT_NAME =RC1.UNIQUE_CONSTRAINT_NAME
  12.  LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC2 ON TC2.CONSTRAINT_NAME =RC1.CONSTRAINT_NAME
  13.  LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CFU on RC1.CONSTRAINT_NAME  = CFU.CONSTRAINT_NAME
  14.  WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'
  15.  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

Section Usefull Admin stuff For The Developer

292 Rating: 2.8/5 (33 votes cast)