SqlSever查询某个表的列名称、说明、备注、注释，类型等信息

A_nanda

已于 2024-04-25 16:47:44 修改

阅读量2.2k

点赞数 32

文章标签： sql 数据库

于 2024-02-03 11:16:48 首次发布

本文链接：https://blog.csdn.net/A_nanda/article/details/136004891

版权

背景：在工程项目中，有时需要对数据查询进行展示，常规的表格展示虽然能解决大部分问题；但在数据量比较大的情况就如果一次完整的展示信息，势必会造成数据加载中增加耗时，影响数据的展示效果；常规的解决方案都是在数据加载中采取分页的模式，降低数据的加载耗时；但如果要一次想知道满足条件的查询的结果有多少，就得翻阅到最后一页。

在我后期维护别人的项目就遇到不采用分页一次加载数据的问题，还好运维人员只是想在数据展示中增加一列，显示对应数据的编号从1开始。

初步设想很简单在原始的查询结果前增减一列行号信息，因此我觉得在很简单，使用row_number()函数。

SELECT
row_number() over(order by Age) as rows,
[ID]
,[Name]
,[Age]
,[Gender]
,[Status]
FROM [HTDWork].[dbo].[test] where ID>2

因此我查询了一下row_number()的简介：如下所示：

ROW_NUMBER()是一个Window函数，它为结果集的分区中的每一行分配一个连续的整数。行号以每个分区中第一行的行号开头。

语法实例：

select *，row_number() over(partition by column1 order by column2) as n
from tablename
在上面语法中：

PARTITION BY子句将结果集划分为分区。 ROW_NUMBER()函数分别应用于每个分区，并重新初始化每个分区的行号。
PARTITION BY子句是可选的。如果未指定，ROW_NUMBER()函数会将整个结果集视为单个分区。
ORDER BY子句定义结果集的每个分区中的行的逻辑顺序。 ORDER BY子句是必需的，因为ROW_NUMBER()函数对顺序敏感

当需求有改动后，如果按照按照名称排序后在按照name分组之后在按照age排序

SELECT
row_number() over(partition by Name order by Age) as rows,
[ID]
,[Name]
,[Age]
,[Gender]
,[Status]
FROM [HTDWork].[dbo].[test]
where ID>2

执行结果如下：

由以上可见row_number()方法在使用是必须跟上row_number() over( order by ColumnName)，这样在使用中就有了限制，微软官网介绍：

对 SQL Server 中的查询返回的数据进行排序。可以使用此子句执行以下操作：

按指定的列列表对查询的结果集进行排序，并有选择地将返回的行限制为指定范围。除非指定 ORDER BY 子句，否则，不能保证在结果集中返回的行的顺序。
确定将排名函数值应用于结果集的顺序。

语法：

ORDER BY order_by_expression

[ COLLATE collation_name ]

[ ASC | DESC ]

[ ,...n ]

[ <offset_fetch> ]

<offset_fetch> ::=

{

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }

[

FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY

]

}

[ ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n ] ]

参数

order_by_expression
指定用于对查询结果集进行排序的列或表达式。可以将排序列指定为一个名称或列别名，也可以指定一个表示列在选择列表中所处位置的非负整数。

可以指定多个排序列。别名必须是唯一的。 ORDER BY 子句中的排序列的顺序定义了排序结果集的结构。也就是说，按第一列对结果集进行排序，然后按第二列对排序列表进行排序，依此类推。

ORDER BY 子句中引用的列名必须明确对应于选择列表中的列或列别名，或对应于 FROM 子句中指定的表中定义的列。如果 ORDER BY 子句引用选择列表中的列别名，则必须单独使用列别名，而不是作为 ORDER BY 子句中的某些表达式的一部分，例如：

SQL复制

SELECT SCHEMA_NAME(schema_id) AS SchemaName FROM sys.objects 
ORDER BY SchemaName; -- correct 
SELECT SCHEMA_NAME(schema_id) AS SchemaName FROM sys.objects 
ORDER BY SchemaName + ''; -- wrong

COLLATE collation_name
指定应根据 collation_name 中指定的排序规则执行 ORDER BY 操作，而不是根据表或视图中所定义的列的排序规则。 collation_name 既可以是 Windows 排序规则名称，也可以是 SQL 排序规则名称。 COLLATE 仅适用于 char、nchar、varchar 和 nvarchar 类型的列。

ASC | DESC
指定按升序或降序排列指定列中的值。 ASC 按从最低值到最高值的顺序进行排序。 DESC 按从最高值到最低值的顺序进行排序。 ASC 是默认排序顺序。 Null 值被视为最低的可能值。

OFFSET { integer_constantoffset_row_count_expression } { ROW | ROWS } |
指定开始从查询表达式返回行之前跳过的行数。该值可以是大于或等于零的整数常量或表达式。

适用于：SQL Server 2012 (11.x) 及更高版本和 Azure SQL 数据库。

offset_row_count_expression 可以是变量、参数或常量标量子查询。在使用子查询时，它无法引用在外部查询范围中定义的任何列。也就是说，它无法与外部查询相关联。

ROW 和 ROWS 是同义词，是为了与 ANSI 兼容而提供的。

在查询执行计划中，将在 TOP 查询运算符的 Offset 属性中显示偏移行数值。

FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
指定在处理 OFFSET 子句后返回的行数。该值可以是大于或等于 1 的整数常量或表达式。

适用于：SQL Server 2012 (11.x) 及更高版本和 Azure SQL 数据库。

fetch_row_count_expression 可以是变量、参数或常量标量子查询。在使用子查询时，它无法引用在外部查询范围中定义的任何列。也就是说，它无法与外部查询相关联。

FIRST 和 NEXT 是同义词，是为了与 ANSI 兼容而提供的。

ROW 和 ROWS 是同义词，是为了与 ANSI 兼容而提供的。

在查询执行计划中，将在 TOP 查询运算符的 Rows 或 Top 属性中显示偏移行数值。

最佳实践

避免将 ORDER BY 子句中的整数指定为选择列表中的列位置表示形式例如，虽然 SELECT ProductID, Name FROM Production.Production ORDER BY 2 等语句是有效的，但与指定实际列名相比，其他人并不容易理解该语句。此外，对选择列表的更改（如更改列顺序或添加新列）需要修改 ORDER BY 子句，以避免出现意外结果。

在 SELECT TOP (N) 语句中，请始终使用 ORDER BY 子句。这是以可预知的方式指明哪些行受 TOP 影响的唯一方法。

互操作性

在与 SELECT...INTO 或 INSERT...SELECT 语句一起使用以从另一来源插入行时，ORDER BY 子句不能保证按指定的顺序插入这些行。

在视图中使用 OFFSET 和 FETCH 并不会更改该视图的 Updateability 属性。

限制和局限

ORDER BY 子句中的列数没有限制；但是，在 ORDER BY 子句中指定的列的总大小不能超过 8,060 个字节。

不能在 ORDER BY 子句中使用 ntext、text、image、geography、geometry 和 xml 类型的列。

order_by_expression 出现在排名函数中时，无法指定整数或常量。

如果已在 FROM 子句中指定了表名的别名，则在 ORDER BY 子句中只能使用该别名来限定其列。

如果 SELECT 语句包含以下子句或运算符之一，则必须在选择列表中定义在 ORDER BY 子句中指定的列名和别名：

UNION 运算符
EXCEPT 运算符
INTERSECT 运算符
SELECT DISTINCT

此外，当语句包含 UNION、EXCEPT 或 INTERSECT 运算符时，必须在第一个（左侧）查询的选择列表中指定列名或列别名。

在使用 UNION、EXCEPT 或 INTERSECT 运算符的查询中，只允许在语句末尾使用 ORDER BY。只有在顶级查询而不是子查询中指定了 UNION、EXCEPT 和 INTERSECT 时，此限制才适用。请参阅后面的“示例”一节。

除非还指定了 TOP 子句或 OFFSET 和 FETCH 子句，否则，视图、内联函数、派生表和子查询中的 ORDER BY 子句无效。在这些对象中使用 ORDER BY 时，该子句仅用于确定由 TOP 子句或 OFFSET 和 FETCH 子句返回的行。 ORDER BY 不保证在查询这些构造时得到有序结果，除非在查询本身中也指定了 ORDER BY。

索引视图或使用 CHECK OPTION 子句定义的视图中不支持 OFFSET 和 FETCH。

可以在允许 TOP 和 ORDER BY 的任何查询中使用 OFFSET 和 FETCH，但具有以下限制：

OVER 子句不支持 OFFSET 和 FETCH。
无法在 INSERT、UPDATE、MERGE 和 DELETE 语句中直接指定 OFFSET 和 FETCH，但可以在这些语句定义的子查询中指定 OFFSET 和 FETCH。例如，在 INSERT INTO SELECT 语句中，可以在 SELECT 语句中指定 OFFSET 和 FETCH。
在使用 UNION、EXCEPT 或 INTERSECT 运算符的查询中，只能在指定查询结果顺序的最终查询中指定 OFFSET 和 FETCH。
TOP 不能与 OFFSET 和 FETCH 在同一个查询表达式（同一个查询作用域）中结合使用。

使用 OFFSET 和 FETCH 限制返回的行数

建议您使用 OFFSET 和 FETCH 子句而不是 TOP 子句实现查询分页解决方案，并限制发送到客户端应用程序的行数。

如果将 OFFSET 和 FETCH 作为分页解决方案，则需要为返回到客户端应用程序的每“页”数据运行一次查询。例如，要以 10 行为增量返回查询结果，您必须执行一次查询以返回 1-10 行，然后再次运行查询以返回 11- 20 行，依此类推。每个查询都是独立的，不会以任何方式与其他查询相关联。这意味着，与使用执行一次查询并在服务器上保持状态的游标不同，将由客户端应用程序负责跟踪状态。若要使用 OFFSET 和 FETCH 在查询请求之间获得稳定的结果，必须满足以下条件：

查询使用的基础数据不能发生变化。即，不会更新查询处理的行，也不会在单个事务中使用快照或可序列化事务隔离执行查询中的所有页面请求。
ORDER BY 子句包含保证是唯一的列或列组合。

刚好项目中引用的是别人的数据库信息，对方设置了权限，无法修改对方表的数据结构；因此，在下面介绍数据库列名称、数据类型、说明、备注、注释等信息如何查询

首先介绍数据库两个系统视图：

information_schema：系统视图

sys.extended_properties：系统视图

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

1. 查询一个表的所有列名

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

Select Name FROM SysColumns Where id=Object_Id('test')

select t.column_name from information_schema.columns t where t.table_name='test';

如图所示:

2. 查询一个表的所有字段的注释

------查询一个表的所有字段的注释
select value from sys.extended_properties where major_id = object_id ('Sys_User' );

如图所示：

3. 查询一个表的所有列名，字段的注释

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

SELECT
A.name AS table_name,
B.name AS column_name,
C.value AS column_description
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 = 'test'

如图所示：

4. 查询一个表的所有列名，数据类型

------查询一个表的所有列名，数据类型

select column_name,data_type from information_schema.columns 
where table_name = 'test'

如图所示：

5. 查询一个表的所有信息

------查询一个表的所有信息

select * FROM information_schema.columns 
where table_name = 'test'

6. 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='test'    --如果只查询指定表,加上此where条件，tablename是要查询的表名；去除where条件查询所有的表信息
order by 
    a.id,a.colorder

如图所示：

创作不易，看到这了，我强烈建议您给于给于我发1元予以支持；您的支持将是我继续创作路线上的动力源泉。谢谢！

A_nanda

关注

32
点赞
踩
24

收藏

觉得还不错? 一键收藏
打赏
1
评论
SqlSever查询某个表的列名称、说明、备注、注释，类型等信息

当需求有改动后，如果按照按照名称排序后在按照name分组之后在按照age排序SELECT[ID],[Name],[Age],[Gender],[Status]where ID>2执行结果如下：由以上可见row_number()方法在使用是必须跟上row_number() over( order by ColumnName)，这样在使用中就有了限制，微软官网介绍：对 SQL Server 中的查询返回的数据进行排序。可以使用此子句执行以下操作：
复制链接

扫一扫