where is the information about Primary Key of a Table?

  • INFORMATION_SCHEMA.COLUMNS can be used to list all the columns of a table. Where is the information about the Primary Key of a table?

    Thanks,

    Data Sheet

  • Look at sys.indexes.

  • Or you can use sp_help 'tablename'.

  • sys.indexes has object_id column, my stored procedure is accepting TableName as parameter.

    How can I get the Primary Key column when I have TableName in parameter but in the column I have its object_id?

     

    Thanks,

    Data Sheet

  • select

        table_name = object_name(parent_object_id),

        PK_name = name

    from sys.objects

    where type='PK'

  • You can get everything you need from the INFORMATION_SCHEMA views, if want to avoid using system tables that may be version-dependant.

    SELECT k.*

    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k

    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c ON k.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA

          AND k.CONSTRAINT_NAME = c.CONSTRAINT_NAME

    WHERE (c.CONSTRAINT_TYPE = 'PRIMARY KEY')

    ORDER BY k.TABLE_SCHEMA, k.TABLE_NAME, k.ORDINAL_POSITION

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply