SQL Server’da Veritabanındaki Tüm Primary Key’leri Listelemek

Herkese merhaba,

Bu yazıda SQL Server’da veritabanındaki tüm Primary Key’lerin nasıl listelenebileceği hakkında bilgi vereceğim.

SQL Server’da bazı durumlarda bu tür sorgular kullanarak tabloları analiz edebiliriz ya da başka amaçlar için de kullanabilirsiniz.

Aşağıdaki sorguyu kullanarak bu işlemi sizler de rahatlıkla yapabilirsiniz.

WITH column_names
AS (SELECT kc.object_id,
           kc.parent_object_id,
           PrimaryKeyName = QUOTENAME(OBJECT_NAME(kc.object_id)),
           SchemaName = QUOTENAME(OBJECT_SCHEMA_NAME(kc.parent_object_id)),
           TableName = QUOTENAME(OBJECT_NAME(kc.parent_object_id)),
           ColumnName = COL_NAME(ob.object_id, ic.column_id),
           ic.key_ordinal,
           key_number = CAST(kc.parent_object_id AS VARCHAR) + CAST(ic.key_ordinal AS VARCHAR),
           parent_number = CAST(kc.parent_object_id AS VARCHAR) + CAST(ic.key_ordinal - 1 AS VARCHAR),
           descending_key = CASE ic.is_descending_key
                                WHEN 0 THEN
                                    'ASC'
                                ELSE
                                    'DESC'
                            END,
           clustered_desc = CASE INDEXPROPERTY(kc.parent_object_id, OBJECT_NAME(kc.object_id), 'IsClustered')
                                WHEN 1 THEN
                                    'CLUSTERED'
                                ELSE
                                    'NONCLUSTERED'
                            END
    FROM sys.key_constraints kc
        INNER JOIN sys.objects ob
            ON kc.parent_object_id = ob.object_id
        INNER JOIN sys.indexes AS I
            ON kc.unique_index_id = I.index_id
               AND kc.parent_object_id = I.object_id
        INNER JOIN sys.index_columns ic
            ON I.object_id = ic.object_id
               AND I.index_id = ic.index_id
    WHERE kc.type = 'PK'
          AND ob.type = 'U'
          AND ob.name NOT IN ( 'dtproperties', 'sysdiagrams' )),
     max_key_ordinal
AS (SELECT object_id,
           parent_object_id,
           max_ordinal_value = MAX(key_ordinal)
    FROM column_names
    GROUP BY object_id,
             parent_object_id),
     primary_key_columns (object_id, parent_object_id, PrimaryKeyName, ColumnNames, SchemaName, TableName, key_ordinal,
                          key_number, clustered_desc
                         )
AS (SELECT pt.object_id,
           pt.parent_object_id,
           pt.PrimaryKeyName,
           ColumnNames = CAST(pt.ColumnName + ' ' + pt.descending_key AS VARCHAR(MAX)),
           pt.SchemaName,
           pt.TableName,
           pt.key_ordinal,
           pt.key_number,
           pt.clustered_desc
    FROM column_names pt
        INNER JOIN column_names ch
            ON pt.key_number = ch.key_number
    WHERE pt.key_ordinal = 1 -- parent record
    UNION ALL
    SELECT pt.object_id,
           pt.parent_object_id,
           pt.PrimaryKeyName,
           CAST(ch.ColumnNames + ', ' + pt.ColumnName + ' ' + pt.descending_key AS VARCHAR(MAX)),
           pt.SchemaName,
           pt.TableName,
           pt.key_ordinal,
           pt.key_number,
           pt.clustered_desc
    FROM column_names pt
        INNER JOIN primary_key_columns ch
            ON pt.parent_number = ch.key_number
    WHERE pt.key_ordinal != 1)
SELECT pk.PrimaryKeyName,
       pk.SchemaName,
       pk.TableName,
       'create_primary_key' = 'ALTER TABLE ' + pk.SchemaName + '.' + pk.TableName + ' ADD CONSTRAINT '
                              + pk.PrimaryKeyName + ' PRIMARY KEY ' + clustered_desc + ' (' + pk.ColumnNames + ') ',
       'drop_primary_key' = 'ALTER TABLE ' + pk.SchemaName + '.' + pk.TableName + ' DROP CONSTRAINT '
                            + pk.PrimaryKeyName
FROM primary_key_columns pk
    INNER JOIN max_key_ordinal mv
        ON mv.object_id = pk.object_id
           AND mv.parent_object_id = pk.parent_object_id
           AND mv.max_ordinal_value = pk.key_ordinal;

Kodu çalıştırdığınızda aşağıdaki sonucu göreceksiniz.

Görüldüğü üzere tüm Primary Key’ler listelenmiş oldu.

Herkese çalışma hayatında ve yaşamında başarılar kolaylıklar.