SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints

Question

I need to write a query on the SQL server to get the list of columns in a particular table, its associated data types (with length) and if they are not null. And I have managed to do this much.

But now I also need to get, in the same table, against a column - TRUE if that column is a primary key.

How do I do this?

My expected output is:

Column name | Data type | Length | isnull | Pk

Answer 1

Use user_type_id instead of system_type_id, in order to avoid duplicate rows for some columns like this:

QUERY:

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')

Note: For SQL Server 2005 and up, you only replace Your TableName with the particular table name.

If you're using schemas, replace YourTableName by YourSchemaName.Then in YourTableName wherever YourSchemaName is there that will be the actual schema name and YourTableName will be the actual table name.