DotNet 笔记

东方欲晓,莫道君起早。

SqlServer数据字典导出脚本

sqlserver数据字典导出脚本的时候,不用配置,直接选定数据库执行以下脚本就行了:

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 ,
        字段说明 = ISNULL(G.[value], '') ,
        标识 = 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, '')
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 <> 'dtproperties'
        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='OrderInfo'    --如果只查询指定表,加上此条件
ORDER BY A.id ,
        A.colorder

 

 

Loading