SQL Serverのビューの列情報を取得する

参考
https://msdn.microsoft.com/ja-jp/library/ms186816.aspx

decimal付近の型サイズを取得する為にxprecなど使いましたがサポートはされていない項目とのこと。

SELECT    o.name AS TableName --テーブル名
    ,
    c.name AS ColumnName --列名
    ,
    t.name AS ColumnTypeName --データ型
    ,
    case
        when t.name = 'nvarchar' then convert(varchar, c.length / 2)
        when t.name = 'nchar' then convert(varchar, c.length / 2)
        when t.name = 'datetime' then '-'
        when t.name = 'bigint' then '-'
        when t.name = 'money' then '-'
        when t.name = 'real' then '-'
        when t.name = 'int' then '-'
        when t.name = 'smallint' then '-'
        when t.name = 'decimal' then convert(varchar, c.xprec) + '.' + convert(varchar, c.xscale)
        when t.name = 'numeric' then convert(varchar, c.length * 2)
        else convert(varchar, c.length)
    end AS ColumnLength --データの長さ
    ,
    case
        when c.isnullable = 1 then 'null'
        else 'null以外'
    end AS ColumnNullOK --null許可=1
FROM
    syscolumns as c
    INNER JOIN
        (
            SELECT
                id,
                xtype,
                name
            FROM
                sysobjects
            WHERE
                xtype IN('U', 'V')
            and sysobjects.name = 'view_interface_purchase_item_sales_item'
        ) AS o
    ON  c.id = o.id
    INNER JOIN
        (
            SELECT
                xtype,
                xusertype,
                type,
                usertype,
                name
            FROM
                systypes
        ) AS t
    ON  c.xtype = t.xtype
    AND c.xusertype = t.xusertype
ORDER BY
    colorder

フォーマットツール
http://www-atl.blog.so-net.ne.jp/