SQL server----sys.objects、sys.columns、sysindexes
简介
sys.objects、sys.columns、sys.indexes这三个都是系统视图,主要映射了表、列、索引等信息。 与MySQL数据库的information_schema库类似。
详解
sys.objects
使用语句 EXEC sp_help 'sys.objects'
查看视图结构信息等,如下:
结构讲解
列名称 |
数据类型 |
说明 |
---|---|---|
name |
sysname |
对象名称。 |
object_id |
int |
对象标识号。 在数据库中是唯一的。 |
principal_id |
int |
如果不是架构所有者,则为单个所有者的 ID。 默认情况下,架构包含的对象由架构所有者拥有。 不过,通过使用 ALTER AUTHORIZATION 语句更改所有权可以指定备用所有者。如果没有备用的单个所有者,则为 NULL。如果对象类型为下列类型之一,则为 NULL:C = CHECK 约束D = DEFAULT(约束或独立)F = FOREIGN KEY 约束PK = PRIMARY KEY 约束R = 规则(旧式,独立)TA = 程序集(CLR 集成)触发器TR = SQL 触发器UQ = UNIQUE 约束EC = Edge 约束 |
schema_id |
int |
包含该对象的架构的 ID。始终包含在 sys 或 INFORMATION_SCHEMA 架构中的架构范围内的系统对象。 |
parent_object_id |
int |
此对象所属对象的 ID。0 = 不是子对象。 |
类型 |
char(2) |
对象类型:AF = 聚合函数 (CLR)C = CHECK 约束D = DEFAULT(约束或独立)F = FOREIGN KEY 约束FN = SQL 标量函数FS = 程序集 (CLR) 标量函数FT = 程序集 (CLR) 表值函数IF = SQL 内联表值函数IT = 内部表P = SQL 存储过程PC = 程序集 (CLR) 存储过程PG = 计划指南PK = PRIMARY KEY 约束R = 规则(旧式,独立)RF = 复制筛选过程S = 系统基表SN = 同义词SO = 序列对象U = 表(用户定义类型)V = 视图适用于:SQL Server 2012 (11.x) 及更高版本。SQ = 服务队列TA = 程序集 (CLR) DML 触发器TF = SQL table-valued-functionTR = SQL DML 触发器TT = 表类型UQ = UNIQUE 约束X = 扩展存储过程适用于:SQL Server 2014 (12.x) 及更高版本、Azure SQL Database、Azure Synapse Analytics、Analytics Platform System (PDW) 。ST = STATS_TREE适用于:SQL Server 2016 (13.x) 及更高版本、Azure SQL Database、Azure Synapse Analytics、Analytics Platform System (PDW) 。ET = 外部表适用于:SQL Server 2017 (14.x) 及更高版本、Azure SQL Database、Azure Synapse Analytics、Analytics Platform System (PDW) 。EC = Edge 约束 |
type_desc |
nvarchar(60) |
对对象类型的说明:AGGREGATE_FUNCTIONCHECK_CONSTRAINTCLR_SCALAR_FUNCTIONCLR_STORED_PROCEDURECLR_TABLE_VALUED_FUNCTIONCLR_TRIGGERDEFAULT_CONSTRAINTEDGE_CONSTRAINTEXTENDED_STORED_PROCEDUREFOREIGN_KEY_CONSTRAINTINTERNAL_TABLEPLAN_GUIDEPRIMARY_KEY_CONSTRAINTREPLICATION_FILTER_PROCEDURERULESEQUENCE_OBJECTSERVICE_QUEUESQL_INLINE_TABLE_VALUED_FUNCTIONSQL_SCALAR_FUNCTIONSQL_STORED_PROCEDURESQL_TABLE_VALUED_FUNCTIONSQL_TRIGGERSYNONYMSYSTEM_TABLETYPE_TABLEUNIQUE_CONSTRAINTUSER_TABLEVIEW |
create_date |
datetime |
对象的创建日期。 |
modify_date |
datetime |
上次使用 ALTER 语句修改对象的日期。 如果对象是表或视图,则创建或更改表或视图上的索引时,modify_date也会更改。 |
is_ms_shipped |
bit |
对象由内部SQL Server组件创建。 |
is_published |
bit |
对象为发布对象。 |
is_schema_published |
bit |
仅发布对象的架构。 |
sys.columns
使用语句 EXEC sp_help 'sys.columns'
查看视图结构信息等,如下:
结构讲解
列名称 |
数据类型 |
说明 |
---|---|---|
object_id |
int |
此列所属对象的 ID。 |
name |
sysname |
列的名称。 在对象中是唯一的。 |
column_id |
int |
列的 ID。 在对象中是唯一的。 列 ID 可以不按顺序排列。 |
system_type_id |
tinyint |
列的系统类型的 ID。 |
user_type_id |
int |
用户定义的列类型的 ID。 若要返回类型的名称,请联接此列上的 sys.types 目录视图。 |
max_length |
smallint |
列的最大长度(字节)。 -1 = 列数据类型是 varchar(max)、nvarchar(max)、varbinary(max) 或 xml。 对于 text、ntext 和 image 列,max_length 值将为 16(仅表示 16 字节指针)或 sp_tableoption 'text in row' 设置的值。 |
精准率 |
tinyint |
如果基于数值,则为该列的精度;否则为 0。 |
scale |
tinyint |
如果基于数值,则为列的小数位数;否则为 0。 |
collation_name |
sysname |
如果基于字符,则为该列排序规则的名称;否则为 NULL。 |
is_nullable |
bit |
1 = 列可为空。 |
is_ansi_padded |
bit |
1 = 如果列为字符、二进制或变量类型,则该列使用 ANSI_PADDING ON 行为。 0 = 列不是字符、二进制或变量类型。 |
is_rowguidcol |
bit |
1 = 列为声明的 ROWGUIDCOL。 |
is_identity |
bit |
1 = 列具有标识值 |
is_computed |
bit |
1 = 列为计算列。 |
is_filestream |
bit |
1 = 列为 FILESTREAM 列。 |
is_replicated |
bit |
1 = 列已复制。 |
is_non_sql_subscribed |
bit |
1 = 列具有非 SQL Server 订阅服务器。 |
is_merge_published |
bit |
1 = 列已合并发布。 |
is_dts_replicated |
bit |
1 = 使用 SSIS 复制列。 |
is_xml_document |
bit |
1 = 内容为完整的 XML 文档。 0 = 内容是文档片段,或列的数据类型不是 xml。 |
xml_collection_id |
int |
如果列的数据类型为 xml 且已输入 XML,则为非零值。 该值将为包含列的验证 XML 架构命名空间的集合的 ID。 0 = 没有 XML 架构集合。 |
default_object_id |
int |
默认对象的 ID,无论该对象是独立对象 sys.sp_bindefault 还是内联的列级 DEFAULT 约束。 内联列级默认对象的 parent_object_id 列是对该表本身的反引用。 0 = 无默认值。 |
rule_object_id |
int |
使用 sys.sp_bindrule 绑定到列的独立规则的 ID。 0 = 无独立规则。 有关列级 CHECK 约束,请参阅 sys.check_constraints (Transact-SQL)。 |
is_sparse |
bit |
1 = 列为稀疏列。 有关详细信息,请参阅 使用稀疏列。 |
is_column_set |
bit |
1 = 列为列集。 有关详细信息,请参阅 使用稀疏列。 |
generated_always_type |
tinyint |
适用于:SQL Server 2016 (13.x) 及更高版本、SQL 数据库。 5、6、7、8 仅适用于 SQL 数据库。 标识生成列值的时间(系统表中的列将始终为 0): 0 = NOT_APPLICABLE 1 = AS_ROW_START 2 = AS_ROW_END5 = AS_TRANSACTION_ID_START6 = AS_TRANSACTION_ID_END7 = AS_SEQUENCE_NUMBER_START8 = AS_SEQUENCE_NUMBER_END 有关详细信息,请参阅时态表(关系数据库)。 |
generated_always_type_desc |
nvarchar(60) |
适用于:SQL Server 2016 (13.x) 及更高版本、SQL 数据库。 generated_always_type 值的文本描述(系统表中的列总是 NOT_APPLICABLE) NOT_APPLICABLE AS_ROW_START AS_ROW_END适用于:从 2022 SQL Server (16.x) 开始,SQL 数据库AS_TRANSACTION_ID_STARTAS_TRANSACTION_ID_ENDAS_SEQUENCE_NUMBER_STARTAS_SEQUENCE_NUMBER_END |
encryption_type |
int |
适用于:SQL Server 2016 (13.x) 及更高版本、SQL 数据库。 加密类型: 1 = 确定性加密 2 = 随机加密 |
encryption_type_desc |
nvarchar(64) |
适用于:SQL Server 2016 (13.x) 及更高版本、SQL 数据库。 加密类型说明: RANDOMIZED DETERMINISTIC |
encryption_algorithm_name |
sysname |
适用于:SQL Server 2016 (13.x) 及更高版本、SQL 数据库。 加密算法的名称。 仅支持 AEAD_AES_256_CBC_HMAC_SHA_512。 |
column_encryption_key_id |
int |
适用于:SQL Server 2016 (13.x) 及更高版本、SQL 数据库。 CEK 的 ID。 |
column_encryption_key_database_name |
sysname |
适用于:SQL Server 2016 (13.x) 及更高版本、SQL 数据库。 列加密密钥所在数据库的名称(如果不在列所在的数据库中)。 NULL(如果密钥与列位于同一数据库中)。 |
is_hidden |
bit |
适用于:SQL Server 2016 (13.x) 及更高版本、SQL 数据库。 指示列是否可以隐藏: 0 = 未隐藏的常规可见列 1 = 隐藏列 |
is_masked |
bit |
适用于:SQL Server 2016 (13.x) 及更高版本、SQL 数据库。 指示列是否由动态数据掩码进行掩码处理: 0 = 不进行掩码处理的常规列 1 = 列进行掩码处理 |
graph_type |
int |
具有一组值的内部列。 对于图形列,这些值介于 1-8 之间;对于其他列,为 NULL。 |
graph_type_desc |
nvarchar(60) |
具有一组值的内部列 |
is_data_deletion_filter_column |
bit |
适用于:Azure SQL Edge。 指示列是否为表的数据保留筛选列。 |
ledger_view_column_type |
tinyint |
适用于:从 2022 SQL Server (16.x) 开始,SQL 数据库。 如果不是 NULL,则指明账本视图中列的类型: 1 = TRANSACTION_ID 2 = SEQUENCE_NUMBER 3 = OPERATION_TYPE 4 = OPERATION_TYPE_DESC有关数据库账本的详细信息,请参阅账本。 |
ledger_view_column_type_desc |
nvarchar(60) |
适用于:从 2022 SQL Server (16.x) 开始,SQL 数据库。 如果不是 NULL,则包含账本视图中列类型的文本描述: TRANSACTION_ID SEQUENCE_NUMBER OPERATION_TYPE OPERATION_TYPE_DESC |
is_dropped_ledger_table_column |
bit |
适用于:从 2022 SQL Server (16.x) 开始,SQL 数据库。 指示已删除的账本表列。 |
sysindexes
使用语句 EXEC sp_help 'sysindexes'
查看视图结构信息等,如下:
结构讲解
列名称 |
数据类型 |
说明 |
---|---|---|
id |
int |
索引所属表的 ID。 |
status |
int |
系统状态信息。 标识为仅供参考。 不支持。 不保证以后的兼容性。 |
first |
binary(6) |
指向第一页或根页的指针。 indid = 0 时未使用。 NULL = Indid> 1 时对索引进行分区。 NULL = indid 为 0 或 1 时对表进行分区。 |
indid |
smallint |
索引 ID: 0 = 堆 1 = 聚集索引 >1 = 非聚集索引 |
root |
binary(6) |
对于 indid>= 1, root 是指向根页的指针。 indid = 0 时未使用。 NULL = Indid> 1 时对索引进行分区。 NULL = indid 为 0 或 1 时对表进行分区。 |
minlen |
smallint |
行的最小大小。 |
keycnt |
smallint |
键数。 |
groupid |
smallint |
在其上创建对象的文件组 ID。 NULL = Indid> 1 时对索引进行分区。 NULL = indid 为 0 或 1 时对表进行分区。 |
dpages |
int |
对于 indid = 0 或 indid = 1, dpages 是所用数据页的计数。 对于 indid> 1, dpages 是使用的索引页计数。 0 = indid> 1 时对索引进行分区。 0 = indid 为 0 或 1 时对表进行分区。 如果发生行溢出,则不会得出准确的结果。 |
reserved |
int |
对于 indid = 0 或 indid = 1, 保留 是为所有索引和表数据分配的页计数。 对于 indid> 1, 保留 是为索引分配的页计数。 0 = indid> 1 时对索引进行分区。 0 = indid 为 0 或 1 时对表进行分区。 如果发生行溢出,则不会得出准确的结果。 |
已使用 |
int |
对于 indid = 0 或 indid = 1, 使用 是用于所有索引和表数据的总页数。 对于 indid> 1, 使用的 是用于索引的页计数。 0 = indid> 1 时对索引进行分区。 0 = indid 为 0 或 1 时对表进行分区。 如果发生行溢出,则不会得出准确的结果。 |
rowcnt |
bigint |
基于 indid = 0 且 indid = 1 的数据级行计数。 0 = indid> 1 时对索引进行分区。 0 = indid 为 0 或 1 时对表进行分区。 |
rowmodctr |
int |
对自上次更新表的统计信息后插入、删除或更新行的总数进行计数。 0 = indid> 1 时对索引进行分区。 0 = indid 为 0 或 1 时对表进行分区。 在 SQL Server 2005 (9.x) 及更高版本中,rowmodctr 与早期版本不完全兼容。 有关详细信息,请参阅“备注”。 |
reserved3 |
int |
返回 0。 标识为仅供参考。 不支持。 不保证以后的兼容性。 |
reserved4 |
int |
返回 0。 标识为仅供参考。 不支持。 不保证以后的兼容性。 |
xmaxlen |
smallint |
行的最大大小 |
maxirow |
smallint |
最大非叶索引行大小。 在 SQL Server 2005 (9.x) 及更高版本中,maxirow 与早期版本不完全兼容。 |
OrigFillFactor |
tinyint |
创建索引时使用的初始填充因子值。 不保留该值;但如果需要重新创建索引但不记得当初使用的填充因子,则该值可能很有帮助。 |
StatVersion |
tinyint |
返回 0。 标识为仅供参考。 不支持。 不保证以后的兼容性。 |
reserved2 |
int |
返回 0。 标识为仅供参考。 不支持。 不保证以后的兼容性。 |
FirstIAM |
binary(6) |
NULL = 索引已分区。 标识为仅供参考。 不支持。 不保证以后的兼容性。 |
impid |
smallint |
索引实现标志。 返回 0。 标识为仅供参考。 不支持。 不保证以后的兼容性。 |
lockflags |
smallint |
用于约束经过考虑的索引锁粒度。 例如,对于本质上是只读的查找表,可以将其设置为仅进行表级锁定以最大限度地降低锁定成本。 |
pgmodctr |
int |
返回 0。 标识为仅供参考。 不支持。 不保证以后的兼容性。 |
keys |
varbinary(816) |
组成索引键的列 ID 列表。 返回 NULL。 若要显示索引键列,请使用 sys.sysindexkeys。 |
name |
sysname |
索引或统计信息的名称。 indid = 0 时返回 NULL。 修改应用程序以查找 NULL 堆名。 |
statblob |
图像 |
统计信息二进制大型对象 (BLOB)。 返回 NULL。 |
maxlen |
int |
标识为仅供参考。 不支持。 不保证以后的兼容性。 |
rows |
int |
基于 indid = 0 且 indid = 1 的数据级行计数,并且对于 indid>1 重复该值。 |
实际使用场景
查询库里所有包含某列名的表
SELECT
o.name,
c.name,
o.object_id,
c.object_id
FROM
sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE
c.name = '列名'
AND o.type = 'U'
SQL
查询库里所有非空表
SELECT
o.name
FROM
sysindexes i
INNER JOIN sys.objects o ON i.id = o.object_id
WHERE
o.type = 'U'
AND i.ROWS > 0;
SQL
查询库里所有包含某列名的非空表
SELECT
o.name
FROM
sysindexes i
INNER JOIN sys.objects o ON i.id = o.object_id
INNER JOIN sys.columns c ON c.object_id = o.object_id
WHERE
o.type = 'U'
AND c.name = '列名'
AND i.ROWS > 0;
SQL
结语
SQL server存在sys.objects和sysobjects、sys.columns和syscolumn、sys.indexes和sysindexes视图,他们之间就差一个点。但是不是一个东西,是不同的视图。 通常没有点的列更多,可以查询的信息更多。