sys.sql_modules (Transact-SQL)sys.sql_modules (Transact-SQL)

适用对象:SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

为在中为 SQL 语言定义的模块的每个对象(包括SQL ServerSQL Server本机编译的标量用户定义函数)返回一行。Returns a row for each object that is an SQL language-defined module in SQL ServerSQL Server, including natively compiled scalar user-defined function. 类型为 P、RF、V、TR、FN、IF、TF 和 R 的对象均有关联的 SQL 模块。Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module. 在此视图中,独立的默认值,即 D 类型的对象也具有 SQL 模块定义。Stand-alone defaults, objects of type D, also have an SQL module definition in this view. 有关这些类型的说明,请参阅sys.databases目录视图中的类型列。For a description of these types, see the type column in the sys.objects catalog view.

有关详细信息,请参阅内存中 OLTP 的标量用户定义函数For more information, see Scalar User-Defined Functions for In-Memory OLTP.

列名称Column name 数据类型Data type 说明Description
object_idobject_id intint 包含对象的对象 ID。ID of the object of the containing object. 在数据库中是唯一的。Is unique within a database.
定义definition nvarchar(max)nvarchar(max) 定义此模块的 SQL 文本。SQL text that defines this module. 还可以使用OBJECT_DEFINITION内置函数获取此值。This value can also be obtained using the OBJECT_DEFINITION built-in function.

NULL = 已加密。NULL = Encrypted.
uses_ansi_nullsuses_ansi_nulls bitbit 模块是使用 SET ANSI_NULLS ON 创建的。Module was created with SET ANSI_NULLS ON.

对于规则和默认值,始终 = 0。Will always be = 0 for rules and defaults.
uses_quoted_identifieruses_quoted_identifier bitbit 模块是使用 SET QUOTED_IDENTIFIER ON 创建的。Module was created with SET QUOTED_IDENTIFIER ON.
is_schema_boundis_schema_bound bitbit 模块是通过 SCHEMABINDING 选项创建的。Module was created with SCHEMABINDING option.

对于本机编译存储过程,始终包含值 1。Always contains a value of 1 for natively compiled stored procedures.
uses_database_collationuses_database_collation bitbit 1 = 架构绑定模块定义取决于正确处理所需的数据库的默认排序规则;否则为 0。1 = Schema-bound module definition depends on the default-collation of the database for correct evaluation; otherwise, 0. 这种依赖关系可防止更改数据库的默认排序规则。Such a dependency prevents changing the database's default collation.
is_recompiledis_recompiled bitbit 已通过重新编译选项创建了过程。Procedure was created WITH RECOMPILE option.
null_on_null_inputnull_on_null_input bitbit 模块被声明为针对任何 NULL 输入生成 NULL 输出。Module was declared to produce a NULL output on any NULL input.
execute_as_principal_idexecute_as_principal_id 整形Int EXECUTE AS 数据库主体的 ID。ID of the EXECUTE AS database principal.

默认情况下,或者 EXECUTE AS CALLER 时,为 NULL。NULL by default or if EXECUTE AS CALLER.

指定主体的 ID (如果以 SELF 身份执行或作为<主体> 执行。ID of the specified principal if EXECUTE AS SELF or EXECUTE AS <principal>.

uses_native_compilationuses_native_compilation bitbit 适用范围SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2014 (12.x)Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2014 (12.x)SQL Server 2014 (12.x).

0 = 非本机编译0 = not natively compiled

1 = 本机编译1 = is natively compiled

默认值为 0。The default value is 0.
is_inlineableis_inlineable bitbit 适用于SQL Server 2019 (15.x)SQL Server 2019 (15.x) 及更高版本。Applies to: SQL Server 2019 (15.x)SQL Server 2019 (15.x) and later.

指示模块是否可内联。Indicates whether the module is inlineable or not. Inlineability 基于此处指定的条件。Inlineability is based on the conditions specified here.

0 = 非可内联0 = not inlineable

1 = 可内联。1 = is inlineable.

对于标量 Udf,如果 UDF 为可内联,则值为 1; 否则为0。For scalar UDFs, the value will be 1 if the UDF is inlineable, and 0 otherwise. 对于内联 Tvf,它始终包含值1,对于所有其他模块类型,该值始终为0。It always contains a value of 1 for inline TVFs, and 0 for all other module types.
inline_typeinline_type bitbit 适用于SQL Server 2019 (15.x)SQL Server 2019 (15.x) 及更高版本。Applies to: SQL Server 2019 (15.x)SQL Server 2019 (15.x) and later.

指示当前是否为模块启用内联。Indicates whether inlining is turned on for the module currently.

0 = 关闭内联0 = inlining is turned off

1 = 开启内联。1 = inlining is turned on.

对于标量 Udf,如果打开内联(显式或隐式),则该值为1。For scalar UDFs, the value will be 1 if inlining is turned on (explicitly or implicitly). 对于内联 Tvf,值始终为1,对于其他模块类型,该值始终为0。The value will always be 1 for inline TVFs, and 0 for other module types.


默认约束(类型为 D 的对象)的 SQL 表达式可在default_constraints sys.databases目录视图中找到。The SQL expression for a DEFAULT constraint, object of type D, is found in the sys.default_constraints catalog view. CHECK 约束的 SQL 表达式(类型为 C 的对象)可在check_constraints sys.databases目录视图中找到。The SQL expression for a CHECK constraint, object of type C, is found in the sys.check_constraints catalog view.

此信息也会在dm_db_uncontained_entities (transact-sql)中介绍。This information is also described in sys.dm_db_uncontained_entities (Transact-SQL).


目录视图中仅显示用户拥有的安全对象的元数据,或用户对其拥有某些权限的安全对象的元数据。The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. 有关详细信息,请参阅 Metadata Visibility ConfigurationFor more information, see Metadata Visibility Configuration.


下面的示例返回当前数据库中每个模块的名称、类型和定义。The following example returns the name, type, and definition of each module in the current database.

SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition  
FROM sys.sql_modules AS sm  
JOIN sys.objects AS o ON sm.object_id = o.object_id  
ORDER BY o.type;  

另请参阅See Also

目录视图 (Transact-SQL) Catalog Views (Transact-SQL)
(Transact-sql)的对象目录视图 Object Catalog Views (Transact-SQL)
查询 SQL Server 系统目录常见问题 Querying the SQL Server System Catalog FAQ
内存中 OLTP(内存中优化)In-Memory OLTP (In-Memory Optimization)