MSSQL Tip: Get Table Primary Key Column(s)
Suppose the following MSSQL server problem. We know table name and need to get its Primary Key columns programmatically. In order to do it we will use the following system tables:
- sysobjects - for all user objects.
- sysindexkeys - for indexes and keys.
- syscolumns - for tables columns.
The query will look like so:
SELECT [name]
FROM syscolumns
WHERE [id] IN (SELECT [id]
FROM sysobjects
WHERE [name] = @table_name)
AND colid IN (SELECT SIK.colid
FROM sysindexkeys SIK
JOIN sysobjects SO ON SIK.[id] = SO.[id]
WHERE SIK.indid = 1
AND SO.[name] = @table_name)
sysindexkeys.indid equals to 1 for clustered indexes.
Sunday, February 1, 2004 2:41 AM
