Post reply

Name of Primary Key columns on a table

  • May 16, 2005 at 7:02 am

    #66115

    Hi,

    I would like to know the way to identify the name of the primary key column(s) on a table (whether single column primary key or multi column primary key) programmatically in SQL Server (looking at something like sysobjects, syscolumns or whatever).

    Thanks in advance

    ourspt

     

  • May 16, 2005 at 7:12 am

    #559004

    i found this on this sites SCRIPTS area.. if this is not what you want you should check it out...

    Find Constraints on a table

    By: rubakuma

    This script would help find primary key, foriegn key, unique constraints,

     check constraints on a table. With slight modification you can use it to find various information about a table.  This Content Sponsored by:  

    select b.COLUMN_NAME

    from INFORMATION_SCHEMA.table_constraints a

    inner join INFORMATION_SCHEMA.key_column_usage b on

     a.CONSTRAINT_CATALOG = b.CONSTRAINT_CATALOG and

     a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and

     a.CONSTRAINT_NAME = b.CONSTRAINT_NAME

    where a.TABLE_CATALOG = '<db Name>'

    and a.TABLE_SCHEMA = '<owner Name'

    and a.TABLE_NAME = '<table Name>'

    and a.CONSTRAINT_TYPE = '<constraint Type>'

    order by b.ordinal_position

     

  • May 16, 2005 at 7:23 am

    #559008

    sp_help 'your table name here'

    Brings back all sorts of information on the table including the primary key.

    LinkedIn Profile

  • May 16, 2005 at 7:42 am

    #559015

    Another way to list a database's all tables with the names of PK constraints:

    select  table_name,

            constraint_name

    from    INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE

    where   objectproperty( object_id( constraint_name ), 'IsPrimaryKey' ) = 1

    Note that the information_schema views only returns info on objects that the executing user has permissions on.

    Also note that all methods to find PK names only works if there are declared primary key constraints - it won't work if you have columns acting as PK's but perhaps only have an uinque index placed on them.

    /Kenneth

  • May 16, 2005 at 4:57 pm

    #559149

    Try using sp_helpIndex.

    HTH Mike

    use Northwind

     

    EXEC sp_helpindex 'employees'

    /*

    Returns

    LastName nonclustered located on PRIMARY LastName

    PK_Employees clustered, unique, primary key located on PRIMARY EmployeeID

    PostalCode nonclustered located on PRIMARY PostalCode

    */

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

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