List all primary keys (PKs) in a database

Rene Castro - Dataedo Team Rene Castro 2018-12-10

Table of Contents:


    The query below lists all primary keys constraints (PK) in the database.

    See also: tables with their primary keys.

    Query

    select schema_name(tab.schema_id) as [schema_name],
        pk.[name] as pk_name,
        substring(column_names, 1, len(column_names)-1) as [columns],
        tab.[name] as table_name
    from sys.tables as tab
        inner join sys.indexes as pk
            on tab.object_id = pk.object_id
            and pk.is_primary_key = 1
        cross apply (select col.[name] + ', '
                    from sys.index_columns as ic
                        inner join sys.columns as col
                            on ic.object_id = col.object_id
                            and ic.column_id = col.column_id
                    where ic.object_id = tab.object_id
                            and ic.index_id = pk.index_id
                    order by col.column_id
                        for xml path ('') ) D (column_names)
    order by schema_name(tab.schema_id),
        pk.[name]
    

    Columns

    • schema_name - PK schema name
    • pk_name - PK constraint name
    • columns - list of PK columns separated with ','
    • table_name - PK table name

    Rows

    • One row: represents one primary key (table) in a database
    • Scope of rows: all PK constraints in a database
    • Ordered by: schema, PK constraint name

    Sample results

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept