|
Execute the following Microsoft SQL Server T-SQL scripts for obtaining the definition of Stored Procedures, Triggers, Functions and Views. INFORMATION_SCHEMA views also have certain definitions with some limitations.
------------
-- SQL Server get Stored Procedure, Trigger, VIEW & Function definitions
------------
USE AdventureWorks2008;
GO
-- SQL Server Stored Procedure definition - get Stored Procedure code - sp_helptext
SELECT SchemaName=schema_name(schema_id),
ObjectName=object_Name(m.object_ID),
ObjectDefinition=definition
FROM sys.SQL_Modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE object_Name(m.object_ID) = 'uspGetBillOfMaterials'
GO
/*
SchemaName ObjectName ObjectDefinition
dbo uspGetBillOfMaterials CREATE PROCEDURE ....
*/
-- T-SQL get VIEW definition
SELECT SchemaName=schema_name(schema_id),
ObjectName=object_Name(m.object_ID),
ObjectDefinition=definition
FROM sys.SQL_Modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE object_Name(m.object_ID) = 'vSalesPersonSalesByFiscalYears'
GO
/*
SchemaName ObjectName ObjectDefinition
Sales vSalesPersonSalesByFiscalYears CREATE VIEW .....
*/
-- System view definition
SELECT OBJECT_DEFINITION(object_id('INFORMATION_SCHEMA.TABLES'))
------------
-- Old way of getting definition code
EXEC sp_helptext 'Sales.vSalesPersonSalesByFiscalYears'
GO
-- Get all object definitions: SPROC, TRIGGER, VIEW & FUNCTION
SELECT SchemaName=schema_name(schema_id),
ObjectName=object_Name(m.object_ID),
ObjectDefinition=definition
FROM sys.SQL_Modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
ORDER BY SchemaName, ObjectName
------------
Related links:
|
