使用SQL语句导出数据库表结构定义，便于生成数据库定义文档

平时开发项目时，经常需要将数据库的表结构记录为文档，便于今后维护时查阅，可以使用以下SQL语句得到表结构定义，方便拷贝到文档：

 

select --t.object_id,
    t.name as tablename
    ,c.name as columnname
    ,case when ty.name='varchar' then 
            case when c.max_length=-1 
                then ty.name + '(max)'
                else ty.name + '(' + convert(varchar, c.max_length)  + ')'
            end
           when ty.name='nvarchar' then 
            case when c.max_length=-1 
                then ty.name + '(max)'
                else ty.name + '(' + convert(varchar, c.max_length/2)  + ')'
            end
           when ty.name='numeric' then 
             ty.name + '(' + convert(varchar, c.precision) + ',' + convert(varchar, c.scale)  + ')'
        else ty.name 
     end as typename
    ,case when c.is_identity=1 
        then 'Yes'
        else 'No'
     end as is_identity
    ,case when c.is_nullable=1 
        then 'Yes'
        else 'No'
     end as is_nullable
    ,c.max_length
    ,c.precision
    ,(select value
        from sys.extended_properties as ex
        where ex.major_id = c.object_id
            and ex.major_id = c.column_id) as notes
    ,scale
from sys.tables as t 
    left join sys.columns as c 
        on c.object_id=t.object_id 
        inner join  
            (select name
                ,system_type_id
             from sys.types 
            where name<>'sysname') as ty 
        on c.system_type_id=ty.system_type_id 
--where t.name like 'VS_%'   --filter table name
order by t.name, c.column_id 

 

 

执行以上语句后的结果