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]

7 comments:

Anonymous said...

視訊,免費視訊,視訊聊天美女,視訊美女聊天,視訊聊天網


視訊美女一對一,視訊聊天俱樂部,視訊聊天戀愛ing,視訊pcgirl,視訊美女超級賣

Dandy 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 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.

International Toll Free Number said...

Pretty great post. I simply stumbled upon your weblog and wished to say that I've really enjoyed browsing your blog posts. In any case I will be subscribing to your feed and I am hoping you write once more very soon!