SQL Server Query to Get the List of Columns in a Table Along with Data Types, Not Null, and Primary Key Constraints

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

To avoid duplicate rows for some columns, use user_type_id instead of system_type_id.

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

Just replace YourTableName with your actual table name - works for SQL Server 2005 and up.

In case you are using schemas, replace YourTableName by YourSchemaName.YourTableName where YourSchemaName is the actual schema name and YourTableName is the actual table name.

SQL Server query to get data type for all columns in table?

SELECT c.name,
       c.max_length,
       c.precision,
       c.scale,
       c.is_nullable,
       t.name
  FROM sys.columns c
  JOIN sys.types   t
    ON c.user_type_id = t.user_type_id
 WHERE c.object_id    = Object_id('dbo.testtable')

Related Topics