How do I determine if a column is in the primary key of its table? (SQL Server)

Question

I am currently using...

select Table_Name, Column_name, data_type, is_Nullable
from information_Schema.Columns

...to determine information about columns in a given database for the purposes of generating a DataAccess Layer.

From where can I retrieve information about if these columns are participants in the primary key of their table?

Foresheet

Answer 1

Here is one way (replace 'keycol' with the column name you are searching for):

SELECT  K.TABLE_NAME ,
    K.COLUMN_NAME ,
    K.CONSTRAINT_NAME
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
        JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON C.TABLE_NAME = K.TABLE_NAME
                                                         AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG
                                                         AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
                                                         AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME
WHERE   C.CONSTRAINT_TYPE = 'PRIMARY KEY'
        AND K.COLUMN_NAME = 'keycol';

Wendelina

You should also have the table name of interest in your where clause. Otherwise, if there is some other table with a Primary key that has the same column name as the primary key of the table you're interested in, you'll get that result. – Pamalapamela

Answer 2

Similarly, the following will give you information about all the tables and their keys, instead of information about specific columns. This way, you make sure you have all the columns of interest and know what they participate in. In order to see all keys (primary, foreign, unique), comment the WHERE clause.

SELECT K.TABLE_NAME, C.CONSTRAINT_TYPE, K.COLUMN_NAME, K.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K
ON C.TABLE_NAME = K.TABLE_NAME
AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG
AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY K.TABLE_NAME, C.CONSTRAINT_TYPE, K.CONSTRAINT_NAME

Linders

Answer 3

For your need, full outer join with INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.KEY_COLUMN_USAGE. At the select statement, add CONSTRAINT_NAME column from INFORMATION_SCHEMA.KEY_COLUMN_USAGE that will give you null or keyname.

select C.Table_Name, C.Column_name, data_type, is_Nullable, U.CONSTRAINT_NAME
from information_Schema.Columns C FULL OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON C.COLUMN_NAME = U.COLUMN_NAME
WHERE C.TABLE_NAME=@TABLENAME

Lowrie

Great answer but you should add TABLE_NAME also in WHERE clause to make sure we are pulling PK from the correct table otherwise it'll load all table's PK while PK field names are same for multiple tables during full outer join. – Baptlsta

This is incorrect as it also returns foreign keys. – Monticule

Answer 4

this query return column with is primary key.

SELECT  col.COLUMN_NAME ,
        col.DATA_TYPE ,
        col.CHARACTER_MAXIMUM_LENGTH ln ,
        CAST(ISNULL(j.is_primary, 0) AS BIT) is_primary
FROM    INFORMATION_SCHEMA.COLUMNS col
        LEFT JOIN ( SELECT  K.TABLE_NAME ,
                            K.COLUMN_NAME ,
                            CASE WHEN K.CONSTRAINT_NAME IS NULL THEN 0
                                 WHEN K.CONSTRAINT_NAME IS NOT NULL THEN 1
                            END is_primary
                    FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
                            JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON C.TABLE_NAME = K.TABLE_NAME
                                                              AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG
                                                              AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
                                                              AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME
                    WHERE   C.CONSTRAINT_TYPE = 'PRIMARY KEY'
                            AND C.TABLE_NAME = 'tablename'
                  ) j ON col.COLUMN_NAME = j.COLUMN_NAME
WHERE   col.TABLE_NAME = 'tablename'

Purser