Archive

Posts Tagged ‘OBJECT_DEFINITION’

SQL Server – Different Ways to Check Object Definition

14.04.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

Using Catalog Views – sys.views

15.06.2011 No comments

sys.views returns a row for each view in the database, sys.views only returns rows for which the user owns or has been granted permission for. Below are some of the columns returned by sys.views.

1. Name – Give name of the view,

2. Schema_id – ID of schema to which the view belongs,

3. Type – 'V' for View,

4. Type_Desc – Textual description of the type,

5. Create_Date – Creation date/time of the view,

6. Modify_Date – Last modification date/time for the view,

7. With_Check_Option – Specifies whether the VIEW is created by specifying WITH CHECK OPTION.

For example,

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

 

USE AdventureWorks2008R2

 

SELECT      Name, Schema_ID, Type_Desc,

            Create_Date, Modify_Date, With_Check_Option

FROM        sys.views

Partial Result Set:

Name                       Schema_ID     Type_Desc     Create_Date                 Modify_Date                 With_Check_Option

————————-  ———–   ———–   ————————-  ————————-      ————————

vAdditionalContactInfo     6             VIEW          2010-12-04 12:29:05.110    2010-12-04 12:29:05.110       0

vEmployee                  5             VIEW          2010-12-04 12:29:05.127    2010-12-04 12:29:05.127       0

vEmployeeDepartment        5             VIEW          2010-12-04 12:29:05.130    2010-12-04 12:29:05.130       0

vIndividualCustomer        9             VIEW          2010-12-04 12:29:05.133    2010-12-04 12:29:05.133       0

vPersonDemographics        9             VIEW          2010-12-04 12:29:05.150    2010-12-04 12:29:05.150       0

vJobCandidate              5             VIEW          2010-12-04 12:29:05.170    2010-12-04 12:29:05.170       0

This view does not return the VIEW definition. To retrieve the VIEW definition, you can user OBJECT_DEFINITION function. This function takes one argument – Object ID, which can be retrieved from sys.views or by using OBJECT_ID function:

SELECT      OBJECT_DEFINITION(OBJECT_ID(N'HumanResources.vEmployee'))

            AS 'Object Definition'

Result Set:

Object Definition

—————————————————-

CREATE VIEW [HumanResources].[vEmployee]

AS

SELECT

e.[BusinessEntityID]

,p.[Title]

,p.[FirstName]

,p.[MiddleName]

,p.[LastName]

,p.[Suffix]

,e.[JobTitle] 

,pp.[PhoneNumber]

,pnt.[Name] AS [PhoneNumberType]

,ea.[EmailAddress]

,p.[EmailPromo

 

(1 row(s) affected)

Hope This Helps!

Vishal