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 SQL server to get the list of columns in a particular table, its associated data types and their length and if they are not null. I have managed to do this much. But now i also need to get in the same table against a column - TRUE if it is a primary key. How do i do this ? This is how the output should be: `Columns_name----Data type----Length----isnull----Pk` please help me!

Answer 1

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