SQL Server 2008 R2：纯SQL查看数据库描述文档

最新推荐文章于 2023-03-28 16:10:13 发布

最新推荐文章于 2023-03-28 16:10:13 发布 · 109 阅读

文章标签：

#数据库

本文提供了一个SQL查询语句，用于查看数据库中表的详细结构、序号、名称、描述、标识、主键、类型、占用字节数、长度、小数位数、是否允许为空、默认值和字段说明。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/***********************************
描述：纯SQL查看数据库描述文档
作者：jehn
联系：jehn@foxmail.com
时间：2011-12-07 14:33:30
************************************/

SELECT

(case when a.colorder=1 then
(
select CAST(表序号 as nvarchar) from
(SELECT d.name,
row_number() over(order by d.name) 表序号
FROM syscolumns a join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
where a.colorder=1) as tmp where name=d.name
)
else '' end) 表序号,
(case when a.colorder=1 then d.name else '' end) 表名,
(case when a.colorder=1 then
(
select value from sys.extended_properties where minor_id=0 and major_id= OBJECT_ID(d.name)
) else '' end) 表描述,
a.colorder 字段序号,
a.name 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in (SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in (SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in (SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name)
)
)
)
)
)
) AND (xtype = 'PK')
) > 0 then '√' else '' end) 主键,
b.name 类型,
a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
--isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
COLUMNPROPERTY(a.id,a.name,'Scale') 小数位数,
(case when a.isnullable=1 then '√'else '' end) 允许空,
isnull(e.text,'') 默认值,
isnull(g.[value],'') AS 字段说明

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 --where d.name='T_SickRoom'
order by d.name,a.colorder

------where d.name='T_SickRoom'也可以指定单张表