Skip to content

sys-sql-modules-transact-sql.md

Latest commit

 

History

History
74 lines (63 loc) · 5.09 KB

sys-sql-modules-transact-sql.md

File metadata and controls

74 lines (63 loc) · 5.09 KB

title: "sys.sql_modules (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "01/09/2018" ms.prod: sql ms.prod_service: "database-engine, sql-database, sql-data-warehouse, pdw" ms.component: "system-catalog-views" ms.reviewer: "" ms.suite: "sql" ms.technology: system-objects ms.tgt_pltfrm: "" ms.topic: "language-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" ms.assetid: 23d3ccd2-f356-4d89-a2cd-bee381243f99 caps.latest.revision: 43 author: edmacauley ms.author: edmaca manager: craigg monikerRange: ">= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || = sqlallproducts-allversions"

sys.sql_modules (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all-md]

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.

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 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;  
GO

See Also

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