Advanced Search

List primary and foreign keys for database

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. SELECT CAST(f.name  AS VARCHAR(255)) AS foreign_key_name
  2.     , r.keycnt
  3.     , CAST(c.name AS  VARCHAR(255)) AS foreign_table
  4.     , CAST(fc.name AS VARCHAR(255)) AS  foreign_column_1
  5.     , CAST(fc2.name AS VARCHAR(255)) AS foreign_column_2
  6.     ,  CAST(p.name AS VARCHAR(255)) AS primary_table
  7.     , CAST(rc.name AS VARCHAR(255))  AS primary_column_1
  8.     , CAST(rc2.name AS VARCHAR(255)) AS  primary_column_2
  9.     FROM sysobjects f
  10.     INNER JOIN sysobjects c ON  f.parent_obj = c.id
  11.     INNER JOIN sysreferences r ON f.id =  r.constid
  12.     INNER JOIN sysobjects p ON r.rkeyid = p.id
  13.     INNER  JOIN syscolumns rc ON r.rkeyid = rc.id AND r.rkey1 = rc.colid
  14.     INNER  JOIN syscolumns fc ON r.fkeyid = fc.id AND r.fkey1 = fc.colid
  15.     LEFT JOIN  syscolumns rc2 ON r.rkeyid = rc2.id AND r.rkey2 = rc.colid
  16.     LEFT JOIN  syscolumns fc2 ON r.fkeyid = fc2.id AND r.fkey2 = fc.colid
  17.     WHERE f.type =  'F'
  18.  -- ORDER BY cast(p.name as varchar(255))
  19.  ORDER BY primary_table

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.