SQL Server: Get table primary key using sql query [duplicate]

Question

I also found another one for SQL Server:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
AND TABLE_NAME = 'TableName' AND TABLE_SCHEMA = 'Schema'

Found another one:

SELECT 
     KU.table_name as TABLENAME
    ,column_name as PRIMARYKEYCOLUMN
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
    ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' 
    AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME 
    AND KU.table_name='YourTableName'

ORDER BY 
     KU.TABLE_NAME
    ,KU.ORDINAL_POSITION
; 

I have tested this on SQL Server 2003/2005

Using SQL SERVER 2005, you can try

SELECT  i.name AS IndexName,
        OBJECT_NAME(ic.OBJECT_ID) AS TableName,
        COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM    sys.indexes AS i INNER JOIN 
        sys.index_columns AS ic ON  i.OBJECT_ID = ic.OBJECT_ID
                                AND i.index_id = ic.index_id
WHERE   i.is_primary_key = 1

Found at SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database

From memory, it's either this

SELECT * FROM sys.objects
WHERE type = 'PK' 
AND  object_id = OBJECT_ID ('tableName')

or this..

SELECT * FROM sys.objects
WHERE type = 'PK' 
AND  parent_object_id = OBJECT_ID ('tableName')

I think one of them should probably work depending on how the data is stored but I am afraid I have no access to SQL to actually verify the same.

SELECT COLUMN_NAME FROM {DATABASENAME}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME LIKE '{TABLENAME}' AND CONSTRAINT_NAME LIKE 'PK%'

WHERE
{DATABASENAME} = your database from your server AND
{TABLENAME} = your table name from which you want to see the primary key.

NOTE : enter your database name and table name without brackets.

select * 
from sysobjects 
where xtype='pk' and 
   parent_obj in (select id from sysobjects where name='tablename')

this will work in sql 2005

The code I'll give you works and retrieves not only keys, but a lot of data from a table in SQL Server. Is tested in SQL Server 2k5/2k8, dunno about 2k. Enjoy!

SELECT DISTINCT
    sys.tables.object_id AS TableId,
    sys.columns.column_id AS ColumnId,
    sys.columns.name AS ColumnName,
    sys.types.name AS TypeName,
    sys.columns.precision AS NumericPrecision,
    sys.columns.scale AS NumericScale,
    sys.columns.is_nullable AS IsNullable,
    (   SELECT 
            COUNT(column_name)
        FROM 
            INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE 
        WHERE
            TABLE_NAME = sys.tables.name AND
            CONSTRAINT_NAME =
                (   SELECT
                    constraint_name
                    FROM 
                        INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                    WHERE
                        TABLE_NAME = sys.tables.name AND                    
                        constraint_type = 'PRIMARY KEY' AND
                        COLUMN_NAME = sys.columns.name
                )
    ) AS IsPrimaryKey,
    sys.columns.max_length / 2 AS CharMaxLength /*BUG*/
FROM 
    sys.columns, sys.types, sys.tables 
WHERE
    sys.tables.object_id = sys.columns.object_id AND
    sys.types.system_type_id = sys.columns.system_type_id AND
    sys.types.user_type_id = sys.columns.user_type_id AND
    sys.tables.name = 'TABLE'
ORDER BY 
    IsPrimaryKey

You can use only the primary key part, but I think that the rest might become handy. Best regards, David