Please tell me how to get primary key name in SQL Server 2005

Question

I need a primary key name and primary key column name of a table please tell me what query should I write..

Answer 1

Not quite what you're looking for, but you can play around with it:

SQL 2000: T-SQL to get foreign key relationships for a table

Answer 2

declare @tableName as nvarchar(100)
set @tableName = 'table'

select i.name, c.name 
from sys.index_columns ic 
    join sys.indexes i on ic.index_id=i.index_id
    join sys.columns c on c.column_id=ic.column_id
where 
    i.[object_id] = object_id(@tableName) and 
    ic.[object_id] = object_id(@tableName) and 
    c.[object_id] = object_id(@tableName) and
    is_primary_key = 1

Answer 3

Here's another option that uses the INFORMATION_SCHEMA views

SELECT 
    cu.Table_Catalog,
    cu.Table_Schema,
    cu.table_name,
    cu.Constraint_name  ,
    cu.column_name

FROM 
    sys.indexes si
    inner join  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu
    on si.name = cu.constraint_name
WHERE 
    is_primary_key = 1

Answer 4

Just a performance note - the select using sys tables is a couple orders of magnitude faster than the select that uses INFORMATION_SCHEMA views