MSSQL – Table/View Column Information

Similar to MySQL – Table/View Column Information, this post will help with MSSQL tables and columns.

exec sp_columns '<tablename>'

Cleaner Result:

SET NOCOUNT ON

DECLARE @tablename NVARCHAR(max)

SET @tablename = <tablename>

SELECT c.NAME 'Column Name'
	,t.NAME 'Data type'
	,c.max_length 'Max Length'
	,
	--c.precision ,
	--c.scale ,
	CASE c.is_nullable
        WHEN 0
            THEN 'No'
        WHEN 1
            THEN 'Yes'
        END AS 'Nullable',
    CASE ISNULL(i.is_primary_key, 0)
        WHEN 0
            THEN 'No'
        WHEN 1
            THEN 'Yes'
        END AS 'Primary Key'
FROM sys.columns c
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
LEFT JOIN sys.index_columns ic ON ic.object_id = c.object_id
	AND ic.column_id = c.column_id
LEFT JOIN sys.indexes i ON ic.object_id = i.object_id
	AND ic.index_id = i.index_id
WHERE c.object_id = OBJECT_ID(@tablename)
	AND t.NAME <> 'sysname'
ORDER BY c.column_id

All Tables Result:

SELECT t.NAME AS 'Table Name',
	c.NAME AS 'Column Name',
	ty.NAME AS 'Data type',
	c.max_length AS 'Max Length',
	CASE c.is_nullable
		WHEN 0
			THEN 'No'
		WHEN 1
			THEN 'Yes'
		END AS 'Nullable',
	CASE ISNULL(i.is_primary_key, 0)
		WHEN 0
			THEN 'No'
		WHEN 1
			THEN 'Yes'
		END AS 'Primary Key'
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
LEFT JOIN sys.index_columns ic ON ic.object_id = c.object_id
	AND ic.column_id = c.column_id
LEFT JOIN sys.indexes i ON ic.object_id = i.object_id
	AND ic.index_id = i.index_id
WHERE ty.NAME <> 'sysname'
ORDER BY 'Table Name',
	c.column_id