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