Wednesday, August 24, 2011

get database columns using mssql server

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]

5 comments:

Anonymous said...

Great one.. Thanks

Anthony said...

Carso, can you please send me "VUdatabase.sql" for your 'Virtual University' project.
Pls Send it to 'aserfes@optonline.net' .. thank you very much.
-ASerfes

Shahana Shafiuddin said...

Why this id 241 came twice?
WHEN columns.system_type_id = 241 THEN 'string'
WHEN columns.system_type_id = 241 THEN 'string'

AGBNY.com said...

Thx sir For blog

http://www.agbny.com/vb

Anonymous said...

Flowers are the true messengers not just for your hearts, but for the concerned appeal and theme of the festival as well. A simple flower can bring the million-watt smile to your loved persons, because flowers are always associated with something very special. Items like confectionaries, foodstuffs, and non-perishables are the ideal companions for flowers, when it’s about forming the ideal gift package. Visit www.flowerstoindiatoday.com to have them all at one click.