Archive

Posts Tagged ‘system_sql_modules’

Using Catalog Views – sys.sql_modules

16.06.2011 No comments

sys.sql_modules returns a row for each function, procedure, trigger, view and rules defined in the database. Below are some of the columns returned by sys.sql_modules:

1. Object_ID – ID of the object in the database,

2. Definition – module text,

3. Uses_ansi_null – 1 if module was created with SET ANSI_NULLS ON, 0 otherwise,

4. Uses_quoted_identifier – 1 if module was created with SET QUOTED_IDENTIFIER ON, 0 otherwise,

5. Is_schema_bound – 1 if module was created with SCHEMABINDING option, 0 otherwise,

6. Is_recompiled – 1 if procedure was created with WITH RECOMPILE option, 0 otherwise,

7. Execute_as_principal_id – this contains user principal ID, which will be used to execute the procedure, this returns NULL when it is EXECUTE AS CALLER, –2 when EXECUTE AS OWNER.

 

— © 2011 – Vishal (http://SqlAndMe.com)

 

USE AdventureWorks2008R2

 

SELECT object_id, definition, uses_ansi_nulls, uses_quoted_identifier,

       is_schema_bound, is_recompiled, execute_as_principal_id

FROM   sys.sql_modules

Partial Result Set:

image

 

While, sys.sql_modules returns user defined objects, sys.system_sql_modules returns the system objects.

SELECT object_id, definition, uses_ansi_nulls, uses_quoted_identifier,

       is_schema_bound, is_recompiled, execute_as_principal_id

FROM   sys.system_sql_modules

Partial Result Set:

image

You can also use sys.all_sql_modules which returns the UNION of sys.sql_modules and sys.system_sql_modules.

Hope This Helps!

Vishal