sys-sql-modules-transact-sql.md

description	sys.sql_modules (Transact-SQL)
title	sys.sql_modules (Transact-SQL)
ms.custom
ms.date	08/20/2021
ms.prod	sql
ms.prod_service	database-engine, sql-database, synapse-analytics, pdw
ms.reviewer
ms.technology	system-objects
ms.topic	reference
f1_keywords	sys.sql_modules_TSQL sql_modules sql_modules_TSQL sys.sql_modules
dev_langs	TSQL
helpviewer_keywords	sys.sql_modules catalog view
author	WilliamDAssafMSFT
ms.author	wiassaf
monikerRange	>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

sys.sql_modules (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw]

Returns a row for each object that is an SQL language-defined module in [!INCLUDEssNoVersion], including natively compiled scalar user-defined function. Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module. Stand-alone defaults, objects of type D, also have an SQL module definition in this view. For a description of these types, see the type column in the sys.objects catalog view.

For more information, see Scalar User-Defined Functions for In-Memory OLTP.

Column name	Data type	Description
object_id	int	ID of the object of the containing object. Is unique within a database.
definition	nvarchar(max)	SQL text that defines this module. This value can also be obtained using the OBJECT_DEFINITION built-in function. NULL = Encrypted.
uses_ansi_nulls	bit	Module was created with SET ANSI_NULLS ON. Will always be = 0 for rules and defaults.
uses_quoted_identifier	bit	Module was created with SET QUOTED_IDENTIFIER ON.
is_schema_bound	bit	Module was created with SCHEMABINDING option. Always contains a value of 1 for natively compiled stored procedures.
uses_database_collation	bit	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_recompiled	bit	Procedure was created WITH RECOMPILE option.
null_on_null_input	bit	Module was declared to produce a NULL output on any NULL input.
execute_as_principal_id	Int	ID of the EXECUTE AS database principal. NULL by default or if EXECUTE AS CALLER. ID of the specified principal if EXECUTE AS SELF or EXECUTE AS <principal>. -2 = EXECUTE AS OWNER.
uses_native_compilation	bit	Applies to: [!INCLUDEssSQL14] through [!INCLUDEssSQL14]. 0 = not natively compiled 1 = is natively compiled The default value is 0.
is_inlineable	bit	Applies to: [!INCLUDEsssql19-md] and later. Indicates whether the module is inlineable or not. Inlineability is based on the conditions specified here. 0 = not inlineable 1 = is inlineable. For scalar user-defined functions (UDFs), the value will be 1 if the UDF is inlineable, and 0 otherwise. It always contains a value of 1 for inline table-valued functions (TVFs), and 0 for all other module types.
inline_type	bit	Applies to: [!INCLUDEsssql19-md] and later. Indicates whether inlining is turned on for the module currently. 0 = inlining is turned off 1 = inlining is turned on. For scalar user-defined functions (UDFs), the value will be 1 if inlining is turned on (explicitly or implicitly). The value will always be 1 for inline table-valued functions (TVFs), and 0 for other module types.

Remarks

The SQL expression for a DEFAULT constraint, object of type D, is found in the sys.default_constraints catalog view. The SQL expression for a CHECK constraint, object of type C, is found in the sys.check_constraints catalog view.

This information is also described in sys.dm_db_uncontained_entities (Transact-SQL).

Permissions

[!INCLUDEssCatViewPerm] For more information, see Metadata Visibility Configuration.

Examples

The following example returns the object_id, schema name, object name, object type, and definition of each module in the current database.

SELECT 
          sm.object_id
        , ss.[name] as [schema]
        , o.[name] 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  
JOIN sys.schemas AS ss
    ON o.schema_id = ss.schema_id  
ORDER BY 
      o.[type]
    , ss.[name]
    , o.[name];  

See Also

Catalog Views (Transact-SQL)
Object Catalog Views (Transact-SQL)
Querying the SQL Server System Catalog FAQ
In-Memory OLTP (In-Memory Optimization)