0

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

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
Dr. Rajesh Rolen
  • 13,643
  • 39
  • 101
  • 175
  • I assume you're working on a generic database utility of some sorts; otherwise you should **know** this information already. I would would write a query against the system tables `sysobects` and `sysindexes` to get this information. However, I am aware that MS have provided useful meta-data views in later versions of SQL Server; and you'd be better off using those. – Disillusioned Jan 07 '10 at 08:05
  • Your question reads as if you expect a primary key to only refer to one column - this is far from always true. And of course, not every table has a PK – MartW Jan 07 '10 at 09:09

4 Answers4

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
Jonas Lincoln
  • 9,327
  • 9
  • 34
  • 48
1

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
Conrad Frix
  • 50,756
  • 12
  • 89
  • 148
0

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

Community
  • 1
  • 1
0

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

soslo
  • 266
  • 2
  • 8