Posted By

sbonnell on 05/19/11


Tagged

key primary keyforeign


Versions (?)

List primary and foreign keys for database


 / Published in: SQL
 

  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  

You need to login to post a comment.