Archive

Posts Tagged ‘sql_modules’

SQL Server – Different Ways to Check Object Definition

April 14, 2014 2 comments

sp_helptext is widely used for checking object definition in SQL Server. sp_helptext can be used to check definition of various database objects like Views, Stored Procedures and User Defined Functions.

There are two other options which can be used to retrieve object definition:

OBJECT_DEFINITION( object_id ) – is a built-in function. It can also retrieve definitions of CHECK/DEFAULT constraints

sys.sql_modules – is a catalog view which returns definitions of all modules in current database

Each of these can be used as follows:


USE [SqlAndMe]
GO

sp_helptext 'MyProcedure'
GO

-- Use OBJECT_ID() function to get object id
SELECT    OBJECT_DEFINITION(OBJECT_ID('MyProcedure'))
GO

-- Use OBJECT_ID() function to get object id
SELECT    [definition]
FROM    sys.sql_modules
WHERE    object_id = OBJECT_ID('MyProcedure')
GO

OBJECT_DEFINITION(object_id) and sys.sql_modules returns results as a single-line when in “Results to Grid” (Ctrl + D) mode. Switch to “Results to Text” (Ctrl + T) for formatted output which will include line breaks.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page –> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Using Catalog Views – sys.sql_modules

June 16, 2011 Leave a comment

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

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe