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