SQL server----sys.objects、sys.columns、sysindexes
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视图，他们之间就差一个点。但是不是一个东西，是不同的视图。 通常没有点的列更多，可以查询的信息更多。