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)