Bir Tablo Üzerinde Tanımlı Index ve Foreign Keylerin Sorgulanması

  • Bir Tablonun Herhangi Bir Kolonunu Foreign Key Kabul Eden Tabloların Sorgulanması

</p>
<p>SELECT [Referans Alan Tablo] = FK.TABLE_NAME,<br />
[Referans Alan Kolon] = CU.COLUMN_NAME,<br />
Constraint_Name = C.CONSTRAINT_NAME<br />
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C<br />
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME<br />
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME<br />
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<br />
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME<br />
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT ON PT.TABLE_NAME = PK.TABLE_NAME<br />
WHERE PK.TABLE_NAME = 'Sorgulanan_Tablo'</p>
<p>

Bu kod sonucunda sorgulanan_tablo tablosunu referans alan foreign keyler döndürülecektir.

 

  • Bir Tablo Üzerine Tanımlı Indexlerin Sorgulanması

</p>
<p>SELECT COL_NAME(ic.object_id,ic.column_id) AS column_name, i.is_unique, i.is_primary_key<br />
FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id WHERE i.object_id = OBJECT_ID('sorgulanan_tablo')</p>
<p>

Bu kod sonucunda sorgulanan_tablo üzerinde tanımlı indexler döndürülecektir.

Yorum Yapın

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Proudly powered by WordPress | Theme: Baskerville 2 by Anders Noren.

Up ↑

%d blogcu bunu beğendi: