MaYaN MaYaN - 2 months ago 8
SQL Question

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.

Answer

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