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

適用於: 是SQL Server 是Azure SQL Database 是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.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 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 整數Int EXECUTE AS 資料庫主體的識別碼。ID of the EXECUTE AS database principal.


識別碼指定的主體如果 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.

指出模組是否為 inlineable。Indicates whether the module is inlineable or not. Inlineability 根據指定的條件此處Inlineability is based on the conditions specified here.

0 = 未 inlineable0 = not inlineable

1 = 是 inlineable。1 = is inlineable.

純量 udf,值會是 1,如果 UDF 因 inlineable,和 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). 值一律為 1 的內嵌 Tvf,以 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. 檢查條件約束,類型 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)