Friday, February 18, 2011

Script To Grab Column Names As UML Properties

I was cooking up some UML diagrams for an existing DB and wanted to get all of the properties. It lists the table, column name in UML markup format and then the data type. The data type column is there so that all I have to do is order it by the second column and I’ll get all of the nulls so I can see if I’m getting a data type that I haven’t handled. The data type conversion is by no means complete but it’s a good start.

Enjoy!

select table_name, '+ ' + column_name + ' : ' +
    CASE WHEN DATA_TYPE in ('ntext', 'nvarchar', 'varchar', 'char') then 'string'
        WHEN DATA_TYPE = 'bit' then 'bool'
        WHEN DATA_TYPE = 'int' then 'int'
        WHEN DATA_TYPE = 'timestamp' then 'timestamp'
        WHEN DATA_TYPE = 'uniqueidentifier' then 'Guid'
        WHEN DATA_TYPE = 'money' then 'decimal'
        WHEN DATA_TYPE in ('smalldatetime', 'datetime') then 'datetime'
        END,
    DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME not like '%aspnet%'
    and TABLE_NAME <> 'sysdiagrams'
order by 1, 2

No comments: