List primary and foreign keys for database


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



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.