sys.sql_modules (Transact-SQL)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric
Returns a row for each object that is a SQL language-defined module in SQL Server, 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 a 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: SQL Server 2014 (12.x) through SQL Server 2014 (12.x). 0 = not natively compiled 1 = is natively compiled The default value is 0. |
is_inlineable | bit | Applies to: SQL Server 2019 (15.x) 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: SQL Server 2019 (15.x) 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).
Renaming a stored procedure, function, view, or trigger won't change the name of the corresponding object in the definition column of the sys.sql_modules
catalog view or the definition returned by the OBJECT_DEFINITION built-in function. For this reason, we recommend that sp_rename
not be used to rename these object types. Instead, drop and re-create the object with its new name. Learn more in sp_rename (Transact-SQL).
Permissions
The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was granted some permission. 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)