Sql Query : SQl Server veritabanında bulunan tüm tablolara ait ilişkiler nasıl bulunur?

Soru :

Sql Server’da bulunan tüm tablolara ait tüm tablo ilişkileri (Primary Key , Foreign Key) tek bir liste halinde nasıl bulunur ?

Cevap :

Danışman olarak çalışan kişilerin sql seviyesinde tablolara ait olan birincil (Primary Key) ve yabancı (Foreign Key) anahtar ilişkilerine hızlı bir şekilde ulaşma ihtiyacı olmuştur . Konu ile ilgili olarak bir blog da karşıma çıkan query’i sizlerle paylaşmakta fayda görüyorum.

SELECT
Tablo = FK.TABLE_NAME,
FK_Kolon = CU.COLUMN_NAME,
PK_Tablo = PK.TABLE_NAME,
PK_Kolon = PT.COLUMN_NAME,
Kısıt_Ismi = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = ‘PRIMARY KEY’
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
No feedback yet
Leave a comment

Your email address will not be revealed on this site.
PoorExcellent
(Line breaks become <br />)
(For my next comment on this site)
(Allow users to contact me through a message form -- Your email will not be revealed!)
This is a captcha-picture. It is used to prevent mass-access by robots.

Please enter the characters from the image above. (case insensitive)

Array
Trackback address for this post
This is a captcha-picture. It is used to prevent mass-access by robots.

Please enter the characters from the image above. (case insensitive)

Array