List SQL Server Table Columns and Data Types

5/5/2014 1:40:34 PM

List all columns and column data types for a specific table in SQL Server. This will also work with views.

DECLARE @TableName NVARCHAR(200)
SET @TableName = 'Users'

SELECT		columns.name 'Column Name',
			types.Name 'Data type',
			columns.max_length 'Max Length',
			columns.precision ,
			columns.scale ,
			columns.is_nullable,
			ISNULL(indexes.is_primary_key, 0) 'Primary Key'
		
FROM		sys.columns columns
			INNER JOIN  sys.types types ON columns.user_type_id = types.user_type_id
			LEFT OUTER JOIN sys.index_columns indexColumns ON indexColumns.object_id = columns.object_id AND indexColumns.column_id = columns.column_id
			LEFT OUTER JOIN	sys.indexes indexes ON indexColumns.object_id = indexes.object_id AND indexColumns.index_id = indexes.index_id

WHERE		columns.object_id = OBJECT_ID(@TableName)

ORDER BY	columns.Name