使用SQL生成指定数据库的数据字典(MSSQL)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | USE DBNAME --指定要生成数据字典的数据库 GO 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 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.xtype= 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='要查询的表' --如果只查询指定表,加上此条件 ORDER BY a.id, a.colorder |
本文来自博客园,作者:一块白板,转载请注明原文链接:https://www.cnblogs.com/ykbb/p/11946967.html
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步