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!
Filed under: Catalog Views, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012