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
  • 732,580
  • 175
  • 1,330
  • 1,459
Dr. Rajesh Rolen
  • 14,029
  • 41
  • 106
  • 178
  • 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,567
  • 9
  • 35
  • 49
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
  • 51,984
  • 12
  • 96
  • 155
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
  • 276
  • 2
  • 8