CODEWITHSUNDEEP
sqlsql-servert-sql
MaYaN
MaYaN

Reputation: 6996

How can I find if a SQL Server column is a primary key in a single SQL statement?

I have a table with a primary key Id:

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Person' AND xtype='U')
    CREATE TABLE Person (
    Id INT PRIMARY KEY NOT NULL, 
    Name NVARCHAR(50) NOT NULL,
    Age INTEGER NOT NULL);
GO

for which I can get all the column details using the following query:

SELECT 
    so.name AS TableName,
    sc.colid AS Id, sc.name AS Name,
    sc.xtype AS TypeId, sc.prec AS Precision, sc.scale AS Scale,
    sc.iscomputed AS IsComputed,
    sc.isnullable AS IsNullable,
    sc.collation AS Collation
FROM syscolumns sc
JOIN sysobjects so ON sc.id = so.id
WHERE so.xtype = 'U' AND so.name = 'Person'

I can also get all the primary keys using:

SELECT * 
FROM sysobjects so 
WHERE xtype = 'PK' 
AND so.parent_obj IN (SELECT id FROM sysobjects WHERE xtype = 'U' AND name = 'Person')

But I cannot figure out how I can combine them all so that I can effectively have a single query with a simple bool or even 1, 0 flag indicating whether a column is a primary key or not.

Something like:

SELECT 
    so.name AS TableName,
    sc.colid AS Id, sc.name AS Name,
    sc.xtype AS TypeId,
    sc.prec AS Precision, sc.scale AS Scale,
    sc.iscomputed AS IsComputed,
    sc.isnullable AS IsNullable,
    sc.collation AS Collation,
    ???isPrimaryKey???
    ...

Any help is very much appreciated.

Upvotes: 1

Views: 69

Answers (2)

ash
ash

Reputation: 329

Try this

SELECT 
    sc.id, 
    so.name AS TableName,
    sc.colid AS Id,
    sc.name AS Name,
    sc.xtype AS TypeId,
    sc.prec AS Precision,
    sc.scale AS Scale,
    sc.iscomputed AS IsComputed,
    sc.isnullable AS IsNullable,
    sc.collation AS Collation,
    cast (case when so1.xtype = 'pk' then 1 else 0 end as bit) as IsPrimaryColumn
FROM syscolumns sc
inner JOIN sysobjects so ON sc.id = so.id
left join sysobjects so1 on sc.id = so1.parent_obj and sc.colid = so1.uid and so1.xType = 'PK'
WHERE 
    so.xtype = 'U'  AND --so1.xtype = 'PK' and
    so.name = 'Person'

Upvotes: 0

John D
John D

Reputation: 1637

You could try this - it finds the appropriate index via sys.key_constraints and uses sys.index_columns to identify the column in the index

SELECT 
    so.name AS TableName,
    sc.colid AS Id,
    sc.name AS Name,
    sc.xtype AS TypeId,
    sc.prec AS Precision,
    sc.scale AS Scale,
    sc.iscomputed AS IsComputed,
    sc.isnullable AS IsNullable,
    sc.collation AS Collation,
    (   select count(*) from sys.key_constraints kc
        inner join sys.indexes i on i.name = kc.name
        inner join sys.index_columns ic on ic.object_id = kc.parent_object_id and ic.index_id = i.index_id
        where kc.type = 'PK' and kc.parent_object_id = so.id and ic.column_id = sc.colid ) as IsPrimaryKey

FROM syscolumns sc
JOIN sysobjects so ON sc.id = so.id
WHERE so.xtype = 'U'
AND so.name = 'Person'

The output is:

TableName   Id  Name    TypeId  Precision   Scale   IsComputed  IsNullable  Collation               IsPrimaryKey
Person      1   Id          56      10          0       0               0    NULL                   1
Person      2   Name        231     50          NULL    0               0    Latin1_General_CI_AS   0
Person      3   Age         56      10          0       0               0    NULL                   0

Upvotes: 1

Related Questions