Results 1 to 4 of 4

Thread: PK columns

  1. #1
    Join Date
    Apr 2003
    Posts
    30

    Unanswered: PK columns

    How to get the list of columns which compose the PK on a table? I want to get it from system tables.

    Thanks.

  2. #2
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81
    select c.name
    from
    sysindexkeys k join syscolumns c
    on k.id = c.id
    and k.colid = c.colid
    where
    k.id = object_id('MyTableName')
    and k.indid=1
    order by k.keyno


    or, simplier:

    select col_name(id,colid)
    from sysindexkeys
    where id = object_id('A3') and indid=1 order by keyno
    --
    kukuk

  3. #3
    Join Date
    Apr 2003
    Posts
    30

    PK not clustered key

    Thanks. But I need to get the Primary key columns not clustered index keys. I think for indid = 1 means cluatered key, but it may not be the primary key.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    If @tblname is null it gives the information about all the tables. If not, set it to a specific table.

    declare @tblname varchar(100)
    set @tblname = NULL
    SELECT TOP 100 PERCENT WITH TIES
    tc.TABLE_NAME
    , kcu.COLUMN_NAME
    , kcu.ORDINAL_POSITION -- Position in the key
    , c.DATA_TYPE
    , c.CHARACTER_MAXIMUM_LENGTH
    , c.CHARACTER_SET_NAME -- typically iso_1 or Unicode
    , c.COLLATION_NAME -- Case/Accent Sensitivity etc.
    , c.NUMERIC_PRECISION -- Digits of data
    , c.NUMERIC_SCALE -- places to right of decimal
    , c.DATETIME_PRECISION
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON tc.TABLE_CATALOG = kcu.TABLE_CATALOG
    AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
    AND tc.TABLE_NAME = kcu.TABLE_NAME
    AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.[COLUMNS] c
    ON tc.TABLE_CATALOG = c.TABLE_CATALOG
    AND tc.TABLE_SCHEMA = c.TABLE_SCHEMA
    AND tc.TABLE_NAME = c.TABLE_NAME
    AND kcu.COLUMN_NAME= c.COLUMN_NAME
    WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND (@tblname is NULL
    OR tc.TABLE_NAME = @tblname)
    AND tc.TABLE_NAME != 'dtproperties'
    ORDER BY tc.TABLE_NAME
    , kcu.ORDINAL_POSITION
    GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •