Wednesday, June 1, 2011

TSQL : SQL 2000 : List Primary Key Columns

SELECT
sysobjects.name AS TableName
,sysindexes.name AS PKName
,syscolumns.colid AS ColumnOrder
,index_col(object_name(sysindexes.id), sysindexes.indid,syscolumns.colid) AS ColumnName
FROM sysobjects 
INNER JOIN sysindexes
ON sysobjects.id = sysindexes.id 
INNER JOIN syscolumns
ON sysindexes.id = syscolumns.id
WHERE syscolumns.colid <= sysindexes.keycnt
AND sysindexes.indid = 1
--AND sysobjects.name = 'tablename'
ORDER BY sysobjects.name ,sysindexes.name  

No comments: