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

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse 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.objects目录视图。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 WITH RECOMPILE 选项创建过程。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 IntInt EXECUTE AS 数据库主体的 ID。ID of the EXECUTE AS database principal.

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

指定主体 if ID EXECUTE AS SELF 或 EXECUTE AS<主体 >。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 is based on the conditions specified here.

0 = 未被内联0 = not inlineable

1 = 是被内联。1 = is inlineable.

对于标量 Udf,值将是 1,如果 UDF 否则是被内联,和 0。For scalar UDFs, the value will be 1 if the UDF is inlineable, and 0 otherwise. 它始终包含内联 Tvf 和对于所有其他模块类型 0 的值为 1。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 表达式中找到sys.default_constraints目录视图。The SQL expression for a DEFAULT constraint, object of type D, is found in the sys.default_constraints catalog view. CHECK 约束,C 类型的对象的 SQL 表达式中找到sys.check_constraints目录视图。The SQL expression for a CHECK constraint, object of type C, is found in the sys.check_constraints catalog view.

此信息还中所述sys.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)