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)
|
