查询数据库表结构
SELECT 表名 = CASE WHEN a.colorder = 1 THEN d.name ELSE '' END ,
字段说明 = ISNULL(g.[value], '') ,
字段名 = a.name ,
类型 = CASE WHEN b.name IN ( 'varchar', 'nvarchar' )
THEN b.name + '('
+ CAST(COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS VARCHAR(4))
+ ')'
WHEN b.name = 'decimal'
THEN b.name + '('
+ CAST(COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS VARCHAR(4))
+ ','
+ CAST(COLUMNPROPERTY(a.id, a.name, 'Scale') AS VARCHAR(4))
+ ')'
ELSE b.name
END
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 = 'j_depot' --可修改表名
ORDER BY a.id , a.colorder