Query to get the column names/types for all tables in SQL 2005

Written by Deepak Vasa on February 11th, 2009

SELECT schemas.name AS [Schema],
tables.name AS [Table],
columns.name AS [Column],
CASE
WHEN columns.system_type_id = 34 THEN ‘byte[]’
WHEN columns.system_type_id = 35 THEN ‘string’
WHEN columns.system_type_id = 36 THEN ‘System.Guid’
WHEN columns.system_type_id = 48 THEN ‘byte’
WHEN columns.system_type_id = 52 THEN ‘short’
WHEN columns.system_type_id = 56 THEN ‘int’
WHEN columns.system_type_id = 58 THEN ‘System.DateTime’
WHEN columns.system_type_id = 59 THEN ‘float’
WHEN columns.system_type_id = 60 THEN ‘decimal’
WHEN columns.system_type_id = 61 THEN ‘System.DateTime’
WHEN columns.system_type_id = 62 THEN ‘double’
WHEN columns.system_type_id = 98 THEN ‘object’
WHEN columns.system_type_id = 99 THEN ‘string’
WHEN columns.system_type_id = 104 THEN ‘bool’
WHEN columns.system_type_id = 106 THEN ‘decimal’
WHEN columns.system_type_id = 108 THEN ‘decimal’
WHEN columns.system_type_id = 122 THEN ‘decimal’
WHEN columns.system_type_id = 127 THEN ‘long’
WHEN columns.system_type_id = 165 THEN ‘byte[]’
WHEN columns.system_type_id = 167 THEN ‘string’
WHEN columns.system_type_id = 173 THEN ‘byte[]’
WHEN columns.system_type_id = 175 THEN ‘string’
WHEN columns.system_type_id = 189 THEN ‘long’
WHEN columns.system_type_id = 231 THEN ‘string’
WHEN columns.system_type_id = 239 THEN ‘string’
WHEN columns.system_type_id = 241 THEN ‘string’
WHEN columns.system_type_id = 241 THEN ‘string’
END AS [Type],
columns.is_nullable AS [Nullable]

FROM sys.tables tables
INNER JOIN sys.schemas schemas ON (tables.schema_id = schemas.schema_id )
INNER JOIN sys.columns columns ON (columns.object_id = tables.object_id)

WHERE tables.name <> ‘sysdiagrams’
AND tables.name <> ‘dtproperties’

ORDER BY [Schema], [Table], [Column], [Type]

Written by Deepak Vasa - Visit Website
 

5 Comments so far ↓

  1. Dan Howard says:

    This is not copy/pastable. The quotes are smart quotes. Can you put it in plain text?

  2. Deepak Vasa says:

    Hi Dan,

    Thanks for pointing it out. I have updated the post as suggested.

    cheers

  3. Peter Vanek says:

    With this querry, you don’t need the case/when for each possilbe datatype:

    SELECT TOP (100) PERCENT sys.sysobjects.name AS [TABLE], sys.syscolumns.name AS [COLUMN], sys.systypes.name AS DATATYPE,
    sys.syscolumns.length AS SIZE, sys.syscolumns.colorder AS RANK, sys.syscolumns.isnullable AS NULLABLE,
    sys.syscolumns.xprec AS PREC, ISNULL(sys.syscolumns.scale, 0) AS SCALE
    FROM sys.sysobjects INNER JOIN
    sys.syscolumns ON sys.sysobjects.id = sys.syscolumns.id INNER JOIN
    sys.systypes ON sys.syscolumns.xtype = sys.systypes.xtype
    WHERE (sys.sysobjects.xtype = ‘U’) AND (sys.systypes.name ‘sysname’)
    ORDER BY [TABLE], sys.syscolumns.colid

  4. Deepak Vasa says:

    Thanks Peter, I think the query missed out an = in the where clause (sys.systypes.name = ‘sysname’). For all the readers out there, please change the sysname above to limit to any data type you want to.

  5. That is genuinely a very advantageous read for me, Have to confess you may possibly be 1 in the most efficient bloggers I at any time saw.Thank you for posting this useful report.

Leave a Comment