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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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