DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use sp_helptext and OBJECT_DEFINITION?

The following system procedures, functions and queries return the source code. Execute the following SQL Server T-SQL scripts in Management Studio Query Editor to find the source code for the stored procedures, view and trigger in the arguments. Set results to text mode.

 

USE AdventureWorks;

go

 

-- SQL stored procedure definition

EXEC sp_helptext 'dbo.uspGetBillOfMaterials';

go

/* Partial results

 

Text

 

CREATE PROCEDURE [dbo].[uspGetBillOfMaterials] 

    @StartProductID [int], 

    @CheckDate [datetime] 

AS 

BEGIN 

    SET NOCOUNT ON; 

*/

 

-- SQL trigger definition

EXEC sp_helptext '[Sales].[uSalesOrderHeader]'

go

/* Partial results

 

Text

 

CREATE TRIGGER [Sales].[uSalesOrderHeader] ON [Sales].[SalesOrderHeader]  

AFTER UPDATE NOT FOR REPLICATION AS  

BEGIN 

    DECLARE @Count int; 

 

    SET @Count = @@ROWCOUNT; 

    IF @Count = 0  

        RETURN; 

 

    SET NOCOUNT ON; 

 

    BEGIN TRY 

    -- Update RevisionNumber for modification of any field EXCEPT the Status. 

        IF NOT UPDATE([Status]) 

        BEGIN 

            UPDATE [Sales].[SalesOrderHeader] 

            SET [Sales].[SalesOrderHeader].[RevisionNumber] =  

                [Sales].[SalesOrderHeader].[RevisionNumber] + 1 

            WHERE [Sales].[SalesOrderHeader].[SalesOrderID] IN  

                (SELECT inserted.[SalesOrderID] FROM inserted); 

        END; 

*/

-- SQL view definition

SELECT [Text]=OBJECT_DEFINITION (OBJECT_ID(N'Person.vStateProvinceCountryRegion'))

go

/* Result

 

Text

  CREATE VIEW [Person].[vStateProvinceCountryRegion]  

  WITH SCHEMABINDING   AS  

  SELECT       sp.[StateProvinceID]       ,sp.[StateProvinceCode]      

  ,sp.[IsOnlyStateProvinceFlag]       ,sp.[Name] AS [StateProvinceName]      

  ,sp.[TerritoryID]       ,cr.[CountryRegionCode]      

  ,cr.[Name] AS [CountryRegionName] 

  FROM [Person].[StateProvince] sp      

  INNER JOIN [Person].[CountryRegion] cr      

  ON sp.[CountryRegionCode] = cr.[CountryRegionCode];

*/

 

-- SQL stored procedure definition - LIMITATION: 4000 bytes

SELECT ROUTINE_DEFINITION, *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE SPECIFIC_NAME = 'uspGetBillOfMaterials'

/* Partial results

 

ROUTINE_DEFINITION                  SPECIFIC_CATALOG  SPECIFIC_NAME

  CREATE PROCEDURE                  AdventureWorks    uspGetBillOfMaterials 

  [dbo].[uspGetBillOfMaterials]       

   @StartProductID [int],     

   @CheckDate [datetime] 

   AS 

   BEGIN     

   SET NOCOUNT ON;       

   -- Use recursive query to generate

  */

------------

 

Related articles:

 

How to get database object definition code?

 

sp_helptext (Transact-SQL)

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE