[转]根据MS SQL数据库表结构生成Schema

　　群里的同事突然想要一个把数据库表结构导出到Excel的工具，之前在公司里有一个小工具可以方便的导出到Excel或者World还有一定的文档格式，着实很省心，现在在外面找不到那小工具，上网搜寻了一下，大概也有一个功能，一下脚本就是网上搜集来的，还不错，自己贴到Excel里再整整格式，还是可以用的：

SELECT 表名 = CASE 
                   WHEN a.colorder = 1 THEN d.name
                   ELSE       ''
              END,
       表说明 = CASE 
                     WHEN a.colorder = 1 THEN ISNULL(f.value, '')
                     ELSE     ''
                END,
       -- 字段序号   = a.colorder,
       字段名 = a.name,
       标识 = CASE 
                   WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√'
                   ELSE       ''
              END,
       主键 = CASE 
                   WHEN EXISTS(
                            SELECT 1
                            FROM   sysobjects
                            WHERE  xtype = 'PK'
                                   AND parent_obj = a.id
                                   AND NAME IN (SELECT NAME
                                                FROM   sysindexes
                                                WHERE  indid IN (SELECT indid
                                                                 FROM   
                                                                        sysindexkeys
                                                                 WHERE  id = a.id
                                                                        AND 
                                                                            colid = 
                                                                            a.colid))
                        ) THEN '√'
                   ELSE       ''
              END,
       类型 = b.name,
       占用字节数 = a.length,
       长度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),
       小数位数 = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0),
       允许空 = CASE 
                     WHEN a.isnullable = 1 THEN '√'
                     ELSE     ''
                END,
       默认值 = ISNULL(e.text, ''),
       字段说明 = ISNULL(g.[value], '')
FROM   syscolumns a
       LEFT JOIN systypes b
            ON  a.xusertype = b.xusertype
       INNER JOIN sysobjects d
            ON  a.id = d.id
            AND d.xtype = 'U'
            AND d.name <> 'DFLWebService'
       LEFT JOIN syscomments e
            ON  a.cdefault = e.id
       LEFT JOIN sys.extended_properties g
            ON  a.id = G.major_id
            AND a.colid = g.minor_id
       LEFT JOIN sys.extended_properties f
            ON  d.id = f.major_id
            AND f.minor_id = 0
                -- where d.name='C_PARTY_SPREADER'    --如果只查询指定表,加上此条件
ORDER BY
       a.id,
       a.colorder

　　

转载于:https://www.cnblogs.com/liver.wang/archive/2013/02/04/2892097.html