SQLServer テーブル定義の取得

テーブル定義 取得用 SQL

select

    c.table_name

,   c.ordinal_position

,   c.column_name

,   c.data_type

,   c.character_maximum_length

,   c.numeric_precision

,   c.numeric_precision_radix

,   c.numeric_scale

,   c.is_nullable

,   c.column_default

,   p.constraint_type

from

    information_schema.columns      c

left outer join

    (

        select

            k.table_catalog

        ,   k.table_schema

        ,   k.table_name

        ,   k.column_name

        ,   t.constraint_type

        from 

            information_schema.key_column_usage     k

        inner join 

            information_schema.table_constraints    t

        on 

            (   t.constraint_name   =   k.constraint_name   )

        where 

            (   t.constraint_type   =   'primary key'       )

    )

    p

on 

        (   c.table_catalog =   p.table_catalog     )

    and (   c.table_schema  =   p.table_schema      )

    and (   c.table_name    =   p.table_name        )

    and (   c.column_name   =   p.column_name       )

order by

    c.table_name

,   c.ordinal_position

http://www.microsoft.com/japan/technet/prodtechnol/sql/2000/books/progref12.mspx