查看代码
WITH cte AS(
SELECT TOP (100) PERCENT CASE WHEN col.colorder = 1 THEN obj.name ELSE obj.name END AS 表名, col.colorder AS 序号,
col.name AS 列名, ISNULL(ep.value, N'') AS 列说明, t.name AS 数据类型, col.length AS 长度,
ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数, CASE WHEN COLUMNPROPERTY(col.id,
col.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识, CASE WHEN EXISTS
(SELECT 1
FROM dbo.sysindexes si INNER JOIN
dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN
dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN
dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
WHERE sc.id = col.id AND sc.colid = col.colid) THEN '√' ELSE '' END AS 主键,
CASE WHEN col.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(comm.text, N'') AS 默认值
FROM dbo.syscolumns AS col LEFT OUTER JOIN
dbo.systypes AS t ON col.xtype = t.xusertype INNER JOIN
dbo.sysobjects AS obj ON col.id = obj.id AND obj.xtype = 'U' AND obj.status >= 0 LEFT OUTER JOIN
dbo.syscomments AS comm ON col.cdefault = comm.id LEFT OUTER JOIN
sys.extended_properties AS ep ON col.id = ep.major_id AND col.colid = ep.minor_id AND
ep.name = 'MS_Description' LEFT OUTER JOIN
sys.extended_properties AS epTwo ON obj.id = epTwo.major_id AND epTwo.minor_id = 0 AND
epTwo.name = 'MS_Description'
ORDER BY obj.name, 序号)
SELECT * FROM cte t1
WHERE t1.表名='product'
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步