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 cINNER JOIN sys.types t ON c.user_type_id = t.user_type_idLEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_idLEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_idWHERE 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.
The stored procedure sp_columns returns detailed table information.
exec sp_columns MyTable
You could use the query:
select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, DATETIME_PRECISION, IS_NULLABLE from INFORMATION_SCHEMA.COLUMNSwhere TABLE_NAME='TableName'
to get all the metadata you require except for the Pk information.