SqlSever查询某个表或某个库的列名称、说明、备注、注释,类型等

database

表或表字段等的注释,是数据库对象的扩展属性。在MSSQL中,支持把一些注释性的内容放到数据库或数据库对象中,增强可读性,有助于日后的管理和维护工作。扩展属性的内容可以通过SSMS添加、修改或删除,也可以通过系统视图查询,通过执行相关的存储过程来维护。

information_schema:系统视图

sys.extended_properties:系统视图

目录

  • 一、sqlserver 查询某个表的列名称、说明、备注、类型等
  • 二、查询一个表的所有列名
  • 三、查询一个表的所有列名,字段的注释
  • 四、查询整个数据库语句

一、sqlserver 查询某个表的列名称、说明、备注、类型等

--sqlserver 查询某个表的列名称、说明、备注、类型等

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 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,

类型 = 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.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 = "数据库表名" --如果只查询指定表,加上此where条件,tablename是要查询的表名;去除where条件查询所有的表信息

order by

a.id,

a.colorder

效果如图:

二、查询一个表的所有列名

--查询一个表的所有列名

Select Name FROM SysColumns Where id=Object_Id("查询的数据库表名")

GO

select t.column_name from information_schema.columns t where t.table_name="查询的数据库表名";

第一局执行效果图:


第二句执行效果图:

三、查询一个表的所有列名,字段的注释

------查询一个表的所有列名,字段的注释

SELECT

A.name AS 表名,

B.name AS 字段名,

C.value AS 字段说明

FROM sys.tables A

INNER JOIN sys.columns B ON B.object_id = A.object_id

LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id

WHERE A.name = "查询的数据库表名"

效果如图:

四、查询整个数据库语句

/************************************************************

* Code formatted by SoftTree SQL Assistant ?v5.0.97

* Time: 2020/3/24 8:50:02

************************************************************/

-- 名名称和字段名

SELECT

(

CASE WHEN a.colorder = 1 THEN 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 小数位数,

(

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

ORDER BY

a.id,

a.colorder

以上是 SqlSever查询某个表或某个库的列名称、说明、备注、注释,类型等 的全部内容, 来源链接: utcz.com/z/535765.html

回到顶部