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 |
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, 类型 = b.name, 长度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION'), 小数位数 = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'),0), 允许为空 = CASE WHEN a.isnullable = 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, 默认值 = ISNULL(e.text, ''), 占用字节数 = a.length, 标识 = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END, 字段描述 = 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 <> '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 b.name IS NOT NULL -- AND d.name='要查询的表' -- 如果只查询表,加上 AND 条件 ORDER BY d.name, a.colorder; -- 验证表数量,避免空表或表中没有ID为1的记录 SELECT name FROM sysobjects WHERE xtype='U'; |
文章出自: 本站技术文章均为原创,版权归 "Desen往事 - 个人博客" 所有;部分图片来源于 Yandex ,转载本站文章请注明来源。
本文标题:MSSQL 查询所有表结构