CODEWITHSUNDEEP
c#.netsqlvb.net
Faulty Orc
Faulty Orc

Reputation: 991

Getting table primary key columns by query

SELECT [name]
FROM syscolumns 
 WHERE [id] IN (SELECT [id] 
                  FROM sysobjects 
                 WHERE [name] = 'ACTIVITY')
   AND colid IN (SELECT SIK.colid 
                   FROM sysindexkeys SIK 
                   JOIN sysobjects SO ON SIK.[id] = SO.[id]  
                    AND SO.[name] = 'ACTIVITY')

It is returning all the columns by id. I need only the primary key. When I add the following line it is empty:

                  WHERE SIK.indid = 1

Why is that? Is there other ways to do the same thing and working? Since I am in the rush

Upvotes: 2

Views: 1306

Answers (3)

Michael Carman
Michael Carman

Reputation: 81

This is the SQL that I use to return the primary key(s) for a table in SQL Server.

DECLARE @TableSchema varchar(255)
SET @TableSchema = 'dbo'
SET @TableName = 'table_name_here'

SELECT 
    ColumnName = col.column_name
FROM    
    information_schema.table_constraints tc               
    INNER JOIN information_schema.key_column_usage col
        ON col.Constraint_Name = tc.Constraint_Name
        AND col.Constraint_schema = tc.Constraint_schema    
WHERE 
    tc.Constraint_Type = 'Primary Key'
    AND col.Table_name = @TableName
    AND col.Table_Schema = @TableSchema

Upvotes: 0

sheikhjabootie
sheikhjabootie

Reputation: 7376

This SQL will find all tables with primary keys:

SELECT 
    i.name AS IndexName,
    OBJECT_NAME(ic.OBJECT_ID) AS TableName,
    COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM 
    sys.indexes AS i
    INNER JOIN sys.index_columns AS ic
        ON i.OBJECT_ID = ic.OBJECT_ID
        AND i.index_id = ic.index_id
WHERE 
    i.is_primary_key = 1

I got it from here. There are some other examples here.

Upvotes: 0

codingbadger
codingbadger

Reputation: 43974

Try this:

Declare @TableName varchar(128)

Set @TableName = 'YourTableName'

SELECT c.COLUMN_NAME as [PrimaryKeyColumns]

FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,              
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE c       
WHERE   pk.TABLE_NAME = @TableName      
AND     CONSTRAINT_TYPE = 'PRIMARY KEY'       
AND     c.TABLE_NAME = pk.TABLE_NAME       
AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

Upvotes: 3

Related Questions