How to Retrieve Column Information Including Primary Key Constraints in SQL Server?

Retrieving Column Information with Primary Key Constraints in SQL Server

Getting a list of columns in a table along with their data types and NOT NULL constraints is a common task. However, you may also need to determine which columns are primary keys. This article addresses how to extend an existing query to include this information.

Extended Query

To incorporate primary key information into your query, use the following extension:

LEFT OUTER JOIN sys.index_columns ic
ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i
ON ic.object_id = i.object_id AND ic.index_id = i.index_id

Additional Column

This extension adds an additional column to the output, indicated as 'Primary Key' in the expected output:

Column Name	Data Type	Length	isNull	Primary Key
...	...	...	...	...

If a column is a primary key, the 'Primary Key' column will display TRUE; otherwise, it will display FALSE.

Full Query

The complete query with the primary key extension is as follows:

SELECT 
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('YourTableName')

Replace 'YourTableName' with your actual table name to retrieve the desired information.

The above is the detailed content of How to Retrieve Column Information Including Primary Key Constraints in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!