【SQL Server】系统目录视图与系统兼容性视图

系统目录视图与系统兼容性视图

SQL Server 早期版本中的许多系统表现在都作为一组视图实现。 这些视图称为兼容性视图，仅用于向后兼容。 兼容性视图公开的元数据在 SQL Server 2000 (8.x) 中也提供。 但是，兼容性视图不公开与在 SQL Server 2005 (9.x) 及更高版本中引入的功能有关的任何元数据。 因此，当您使用新功能（例如 Service Broker 或分区）时，必须切换到使用目录视图。
升级到目录视图的另一个原因是，存储用户 ID 和类型 ID 的兼容性视图列可能返回 NULL 或触发算术溢出。 这是因为您可以创建超过 32,767 个用户、组和角色，以及超过 32,767 种数据类型。 例如，如果您要创建32768用户，然后运行以下查询： SELECT * FROM sys.sysusers 。 如果 ARITHABORT 设置为 ON，则查询会失败，并出现算术溢出错误。 如果 ARITHABORT 设置为 OFF，则 uid 列返回 NULL。

数据表结构相关

sys.objects

在数据库内创建的每个用户定义的架构范围内的对象（包括本机编译的标量用户定义函数）都包含一行。

字段	数据类型	描述
name	nvarchar(256)	对象名称
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 = 不是子对象。
type	char(2)	对象类型： AF = 聚合函数 (CLR) C = CHECK 约束 D = DEFAULT（约束或独立） F = FOREIGN KEY 约束 FN = SQL 标量函数 FS = 程序集 (CLR) 标量函数 FT = 程序集 (CLR) 表值函数 IF = SQL 内联表值函数 IT = 内部表 P = SQL 存储过程 PC = Assembly (CLR) 存储过程 PG = 计划指南 PK = PRIMARY KEY 约束 R = 规则（旧式，独立） RF = 复制筛选过程 S = 系统基表 SN = 同义词 SO = 序列对象 U = 表（用户定义类型） V = 视图 EC = Edge 约束 适用于：SQL Server 2012 (11.x) 及更高版本。 SQ = 服务队列 TA = 程序集 (CLR) DML 触发器 TF = SQL 表值函数 TR = SQL DML 触发器 TT = 表类型 UQ = UNIQUE 约束 X = 扩展存储过程 适用于： SQL Server 2016 (13.x) 和更高版本和 Azure SQL 数据库 Azure Synapse Analytics（SQL 数据仓库） 并行数据仓库 。 ET = 外部表
type_desc	nvarchar(120)	对对象类型的说明
create_date	datetime	对象的创建日期。
modify_date	datetime	上次使用 ALTER 语句修改对象的日期。 如果对象是表或视图，则在创建或更改表或视图的索引时，modify_date 也会发生更改。
is_ms_shipped	bit	对象由内部 SQL Server 组件创建。
is_published	bit	对象为发布对象。
is_schema_published	bit	仅发布对象的架构。

sys.columns

为包含列的对象（如视图或表）的每一列返回一行。 下面是包含列的对象类型的列表。

• 表值程序集函数 (FT)
• 内联表值 SQL 函数 (IF)
• 内部表 (IT)
• 系统表 (S)
• 表值 SQL 函数 (TF)
• 用户表 (U)
• 视图 (V)

字段	数据类型	描述
object_id	int	此列所属对象的 ID。
name	nvarchar(256)	列的名称。 在对象中是唯一的。
column_id	int	列的 ID。 在对象中是唯一的。 列 ID 可以不按顺序排列。
system_type_id	tinyint	列的系统类型的 ID。
user_type_id	int	用户定义的列类型的 ID。
max_length	smallint	列的最大长度（字节）。 -1 = 列数据类型为 **varchar (max) **、 **nvarchar (max) **、 **varbinary (max) **或 xml。 对于 text 列，max_length 值将是16，或者是 sp_tableoption “text in row” 设置的值。
precision	tinyint	如果基于数值，则为该列的精度；否则为 0。
scale	tinyint	如果基于数值，则为列的小数位数；否则为 0。
collation_name	nvarchar(256)	如果基于字符，则为该列排序规则的名称；否则为 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，无论该对象是独立的对象 sp_bindefault sys.databases还是内联的列级默认约束。 内联列级默认对象的 parent_object_id 列是对该表本身的反引用。 0 = 无默认值。
rule_object_id	int	使用 sys.sp_bindrule 绑定到列的独立规则的 ID。 0 = 无独立规则。 有关列级检查约束，
is_sparse	bit	1 = 列为稀疏列。
is_column_set	bit	1 = 列为列集。
generated_always_type	tinyint	适用于：SQL Server 2016 (13.x) 及更高版本、SQL 数据库。 标识生成列值 (对于系统表中的列，将始终为 0) ： 0 = NOT_APPLICABLE 1 = AS_ROW_START 2 = AS_ROW_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
encryption_type	int	适用于：SQL Server 2016 (13.x) 及更高版本、SQL 数据库。 加密类型： 1 = 确定性加密 2 = 随机加密
encryption_type_desc	nvarchar (64)	适用于：SQL Server 2016 (13.x) 及更高版本、SQL 数据库。 加密类型说明： 随机化 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 2019 (15.x) 及更高版本、SQL 数据库。 指示是否隐藏列： 0 = 常规、非隐藏、可见列 1 = 隐藏列
is_masked	bit	适用于：SQL Server 2019 (15.x) 及更高版本、SQL 数据库。 指示是否由动态数据掩码屏蔽列： 0 = 常规、非掩码列 1 = 列被屏蔽

sys.types

每个系统类型和用户定义类型都在表中对应一行。

字段	数据类型	描述
name	sysname	类型的名称。 在架构内是唯一的。
system_type_id	tinyint	类型的内部系统类型的 ID。
user_type_id	int	类型的 ID。 在该数据库中是唯一的。 对于系统数据类型， user_type_id = system_type_id。
schema_id	int	类型所属架构的 ID。
principal_id	int	如果个体所有者与架构所有者不同，则表示该所有者的 ID。 默认情况下，架构包含的对象由架构所有者拥有。 不过，通过使用 ALTER AUTHORIZATION 语句更改所有权可以指定备用所有者。 如果没有另外的个体所有者，则值为 NULL。
max_length	smallint	类型的最大长度（字节）。 -1 = 列数据类型为 **varchar (max) **、 **nvarchar (max) **、 **varbinary (max) **或 xml。 对于 text 列， max_length 值将为16。
precision	tinyint	如果类型基于数值，则表示类型的最大精度；否则，该值为 0。
scale	tinyint	如果类型基于数值，则表示类型的最大小数位数；否则，该值为 0。
collation_name	sysname	如果类型基于字符，则表示类型排序规则的名称；否则，该值为 NULL。
is_nullable	bit	类型可以为 Null。
is_user_defined	bit	1 = 用户定义类型。 0 = SQL Server 系统数据类型
is_assembly_type	bit	1 = 类型的实现是在 CLR 程序集中定义的。 0 = 类型基于 SQL Server 系统数据类型。
default_object_id	int	使用 sp_bindefault绑定到类型的独立默认的 ID。 0 = 不存在默认值。
rule_object_id	int	使用 sp_bindrule绑定到类型的独立规则的 ID。 0 = 不存在规则。
is_table_type	bit	指示该类型为表。

sys.indexes

每个表格对象（例如，表、视图或表值函数）的索引或堆都包含一行。

字段	数据类型	描述
object_id	int	该索引所属对象的 ID。
name	sysname	索引的名称。 名称 仅在对象中是唯一的。 NULL = 堆
index_id	int	索引的 ID。 index_id 仅在对象中是唯一的。 0 = 堆 1 = 聚集索引 > 1 = 非聚集索引
type	tinyint	索引的类型： 0 = 堆 1 = 聚集 2 = 非聚集 3 = XML 4 = 空间 5 = 聚集列存储索引。 适用于：SQL Server 2014 (12.x) 及更高版本。 6 = 非聚集列存储索引。 适用于：SQL Server 2012 (11.x) 及更高版本。 7 = 非聚集哈希索引。 适用于：SQL Server 2014 (12.x) 及更高版本。
type_desc	nvarchar(120)	索引类型的说明： HEAP CLUSTERED NONCLUSTERED XML SPATIAL 聚集列存储- 适用于： SQL Server 2014 (12.x) 和更高版本。 非聚集列存储- 适用于： SQL Server 2012 (11.x) 和更高版本。
is_unique	bit	1 = 索引是唯一的。 0 = 索引不是唯一的。 对于聚集列存储索引始终为 0。
data_space_id	int	此索引的数据空间的 ID。 数据空间是文件组或分区方案。 0 = object_id 是表值函数或内存中索引。
ignore_dup_key	bit	1 = IGNORE_DUP_KEY 是 ON。 0 = IGNORE_DUP_KEY 是 OFF。
is_primary_key	bit	1 = 索引是 PRIMARY KEY 约束的一部分。 对于聚集列存储索引始终为 0。
is_unique_constraint	bit	1 = 索引是 UNIQUE 约束的一部分。 对于聚集列存储索引始终为 0。
fill_factor	tinyint	> 0 = 创建或重新生成索引时使用的 FILLFACTOR 百分比。 0 = 默认值 对于聚集列存储索引始终为 0。
is_padded	bit	1 = PADINDEX 是 ON。 0 = PADINDEX 是 OFF。 对于聚集列存储索引始终为 0。
is_disabled	bit	1 = 禁用索引。 0 = 不禁用索引。
is_hypothetical	bit	1 = 索引是假设的，不能直接用作数据访问路径。 假设的索引包含列级统计信息。 0 = 索引不是假设的。
allow_row_locks	bit	1 = 索引允许行锁。 0 = 索引不允许行锁。 对于聚集列存储索引始终为 0。
allow_page_locks	bit	1 = 索引允许页锁。 0 = 索引不允许页锁。 对于聚集列存储索引始终为 0。
has_filter	bit	1 = 索引具有一个筛选器，且仅包含符合筛选器定义的行。 0 = 索引不具有筛选器。
filter_definition	nvarchar(max)	包含在筛选索引中的行子集的表达式。 对于堆、非筛选索引或表的权限不足，则为 NULL。
auto_created	bit	1 = 自动优化创建索引。 0 = 索引是由用户创建的。
optimize_for_sequential_key	bit	1 = 索引已启用上一页插入优化。 0 = 默认值。 索引已禁用上一页插入优化。

sys.index_columns

字段	数据类型	描述
object_id	int	定义索引所依据的对象的 ID。
index_id	int	定义了列的索引的 ID。
index_column_id	int	索引列的 ID。 index_column_id 仅在 index_id中是唯一的。
column_id	int	Object_id中的列的 ID。 0 = 非聚集索引中的行标识符 (RID)。 column_id 仅在 object_id中是唯一的。
key_ordinal	tinyint	键列集内的序数（从 1 开始）。 0 = 不是键列，或者是 XML 索引、列存储索引或空间索引。 注意： XML 索引或空间索引不能是键，因为基础列不是可比较的，这意味着不能对其值进行排序。
partition_ordinal	tinyint	分区列集内的序数（从 1 开始）。 聚集列存储索引可以具有最多 1 个分区列。 0 = 非分区列。
is_descending_key	bit	1 = 索引键列采用降序排序。 0 = 索引键列的排序方向为升序，或者列是列存储或哈希索引的一部分。
is_included_column	bit	1 = 列是使用 CREATE INDEX INCLUDE 子句添加到索引的非键列，或者列是列存储索引的一部分。 0 = 列不是包含列。 因为列是聚集键的一部分而隐式添加的列未列在 index_columns中。 由于是分区列而隐式添加的列作为 0 返回。

应用示例

判断对象是否存在

-- 判断数据表是否存在
IF OBJECT_ID('tableName', 'U') IS NOT NULL
	PRINT ('1');

-- 判断存储过程
IF OBJECT_ID('procedure', 'P') IS NOT NULL 
	PRINT('1');

-- 判断触发器是否存在
IF OBJECT_ID('trigger'， 'TR') IS NOT NULL
	PRINT('1');

-- 判断数据库是否存在
IF DB_ID('DBName') IS NOT NULL
	PRINT('1');

-- 判断数据表中字段是否存在
IF COL_LENGTH('tableName', 'colName') IS NOT NULL
	PRINT('1');

查询数据表结构

BEGIN
	DECLARE @tableName varchar(128) = 'tableName'

	;WITH primaryKeys AS (
		SELECT
			cols.name [primaryKey],
			'PK' [name]
		FROM
			sys.index_columns indexCols
		INNER JOIN sys.columns cols ON indexCols.object_id = cols.object_id AND indexCols.column_id = cols.column_id
		INNER JOIN sys.indexes inds ON indexCols.object_id = inds.object_id AND indexCols.index_id = inds.index_id
		WHERE indexCols.object_id = OBJECT_ID(@tableName, 'u') AND inds.is_primary_key = 1
	)

	SELECT
		cols.name [字段],
		types.name [数据类型],
		(CASE WHEN cols.length = types.length THEN '' WHEN cols.length = '-1' THEN 'max' ELSE CONVERT(varchar, cols.length) END) [长度],
		(CASE WHEN cols.isnullable = 0 THEN 'Y' ELSE '' END) [非空],
		ISNULL(pk.name, '') [主键]
	FROM
		syscolumns cols
	INNER JOIN systypes types ON cols.xtype = types.xtype
	LEFT JOIN primaryKeys pk ON pk.primaryKey = cols.name
	WHERE 
		cols.id = OBJECT_ID(@tableName, 'U') 
	ORDER BY cols.colorder

END

创建与原数据表结构完全相同的流水分表

/**
	description:创建与主库中结构、索引相同的数据表
	param:@tableName:数据表表名
		  @DBName:需要创建新表的数据库库名
*/
ALTER PROCEDURE [dbo].[sp_CreateListTableByNameType]
(
	@tableName varchar(128),
	@DBName varchar(128)
)
AS
BEGIN
	IF OBJECT_ID('tempdb..#temp_indexCols', 'u') IS NOT NULL DROP TABLE #temp_indexCols

	SELECT
		indexes.name AS index_name,
		indexes.type_desc AS type_desc,
		cols.name AS column_name,
		indCols.is_descending_key AS is_descending_key
	INTO #temp_indexCols
	FROM
		sys.index_columns indCols
	INNER JOIN sys.indexes indexes ON indCols.object_id = indexes.object_id AND indCols.index_id = indexes.index_id
	INNER JOIN sys.columns cols ON indCols.object_id = cols.object_id AND indCols.column_id = cols.column_id
	WHERE indCols.object_id = OBJECT_ID(@tableName, 'u')
	ORDER BY indCols.index_id, indCols.key_ordinal

	DECLARE @index_name varchar(128)
	DECLARE @SQL varchar(max) = ''

	SET @SQL += '
		SELECT * INTO ['+ @DBName +'].[dbo].['+ @tableName +'] FROM [dbo].['+ @tableName +'] WHERE 1 = 2;
	'

	DECLARE cur_CreateTable CURSOR FOR
	SELECT DISTINCT index_name FROM #temp_indexCols

	OPEN cur_CreateTable
	FETCH NEXT FROM cur_CreateTable INTO @index_name

	WHILE @@FETCH_STATUS = 0
	BEGIN	
		IF EXISTS(SELECT 1 FROM #temp_indexCols WHERE type_desc = 'CLUSTERED' AND index_name = @index_name)
		BEGIN
			SET @SQL += 'ALTER TABLE ['+ @DBName +'].[dbo].['+ @tableName +'] ADD CONSTRAINT ['+ @index_name +'] PRIMARY KEY CLUSTERED('
			
			SELECT @SQL += '['+ column_name +'], '
			FROM #temp_indexCols
			WHERE index_name = @index_name
			
			SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1) + ') WITH (FILLFACTOR = 90);'
			
		END
		
		IF EXISTS(SELECT 1 FROM #temp_indexCols WHERE type_desc = 'NONCLUSTERED' AND index_name = @index_name)
		BEGIN
			SET @SQL += 'CREATE INDEX ['+ @index_name +'] ON ['+ @DBName +'].[dbo].['+ @tableName +'] ('
			
			SELECT @SQL += '['+ column_name +']'+ (CASE WHEN is_descending_key = 1 THEN ' DESC, ' WHEN is_descending_key = 0 THEN ' ASC, ' ELSE '' END)
			FROM #temp_indexCols
			WHERE index_name = @index_name
			
			SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1) + ') WITH (FILLFACTOR = 90);'

		END
		
		FETCH NEXT FROM cur_CreateTable INTO @index_name
	END

	CLOSE cur_CreateTable
	DEALLOCATE cur_CreateTable

	EXEC(@SQL)
	
	IF OBJECT_ID('tempdb..#temp_indexCols', 'u') IS NOT NULL DROP TABLE #temp_indexCols

END

定时作业相关

sysjobs

存储将由 SQL Server 代理执行的各个预定作业的信息。 该表存储在 msdb 数据库中

字段	数据类型	描述
job_id	uniqueidentifier	作业的唯一 ID。
originating_server_id	int	发出作业的服务器的 ID。
name	sysname	作业的名称。
enabled	tinyint	指示是否启用要执行的作业。
description	nvarchar(512)	对作业的说明。
start_step_id	int	执行作业的起始步骤的 ID。
category_id	int	作业类别的 ID。
owner_sid	varbinary(85)	作业所有者的安全标识号 (SID)。
notify_level_eventlog	int	位掩码 ，指示在何种情况下，通知事件应记录到 Microsoft Windows 应用程序日志中： 0 = 从不 1 = 作业成功时 2 = 作业失败时 3 = 无论作业完成与否 (无论作业结果如何)
notify_level_email	int	位掩码，指示在何种情况下应在作业完成时发送通知电子邮件： 0 = 从不 1 = 作业成功时 2 = 作业失败时 3 = 无论作业完成与否 (无论作业结果如何)
notify_level_netsend	int	位掩码，指示在何种情况下应在作业完成时发送网络消息： 0 = 从不 1 = 作业成功时 2 = 作业失败时 3 = 无论作业完成与否 (无论作业结果如何)
notify_level_page	int	位掩码，指示在何种情况下应在作业完成时发送寻呼： 0 = 从不 1 = 作业成功时 2 = 作业失败时 3 = 无论作业完成与否 (无论作业结果如何)
notify_email_operator_id	int	被通知的操作员的电子邮件名称。
notify_netsend_operator_id	int	发送网络消息时使用的计算机或用户的 ID。
notify_page_operator_id	int	发送寻呼时使用的计算机或用户的 ID。
delete_level	int	位掩码 ，指示在何种情况下应在作业完成时删除作业： 0 = 从不 1 = 作业成功时 2 = 作业失败时 3 = 无论作业完成与否
date_created	datetime	作业的创建日期。
date_modified	datetime	上次修改作业的日期。
version_number	int	作业版本。

sysjobservers

存储特定作业与一个或多个目标服务器的关联或关系。 该表存储在 msdb 数据库中。

字段	数据类型	描述
job_id	uniqueidentifier	作业标识号。
server_id	int	服务器标识号。
last_run_outcome	tinyint	作业上次运行的结果： 0 = 失败 1 = 成功 2 = 重试 3 = 取消 4 = 正在进行 5 = 未知 (请参阅下面的 “备注” 部分)
last_outcome_message	nvarchar	与 last_run_outcome 列关联的消息（如果有）。
last_run_date	int	上次运行作业的日期。
last_run_time	int	上次运行作业的时间。
last_run_duration	int	作业运行的持续时间，以小时、分钟和秒为单位。 使用公式计算： (小时 * 10000) + (分钟 * 100) +秒。