Edit

Share via

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.

Always 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 0 = not natively compiled
1 = is natively compiled

The default value is 0.

Applies to: SQL Server 2014 (12.x) and later versions.
is_inlineable bit Indicates whether the module can be inlined or not. Inlineability is based on the conditions specified in the requirements.

0 = can't be inlined
1 = can be inlined.

For scalar user-defined functions (UDFs), the value is 1 if the UDF can be inlined, and 0 otherwise. It always contains a value of 1 for inline table-valued functions (TVFs), and 0 for all other module types.

Applies to: SQL Server 2019 (15.x) and later versions.
inline_type bit 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 is 1 if inlining is turned on (explicitly or implicitly). The value is always 1 for inline table-valued functions (TVFs), and 0 for other module types.

Applies to: SQL Server 2019 (15.x) and later versions.

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.

Renaming a stored procedure, function, view, or trigger doesn'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 you don't use sp_rename to rename these object types. Instead, drop and recreate the object with its new name. Learn more in sp_rename.

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

Related content