Ever wondered how a simple task can be done multiple ways. You can script a stored procedure in many ways. You can use SSMS tool and use Generate Script option. However, if you want to do it in T-SQL, there are four ways.
Let us create this simple procedure
CREATE PROCEDURE TESTING
(@OPTION INT)
AS
IF @OPTION=0
SELECT 1 AS NUMBER
ELSE
SELECT 100 AS NUMBER
GO
Now if you want to know the script of the above procedure, you can use the following methods
1 Use SP_HELPTEXT System Stored Procedure
EXEC sp_HELPTEXT TESTING
2 Use ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES view
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='TESTING'
3 Use OBJECT_DEFINITION function
SELECT OBJECT_DEFINITION(OBJECT_ID('TESTING'))
4 Use SYS.SYSCOMMENTS system view
SELECT TEXT FROM SYS.SYSCOMMENTS WHERE ID=OBJECT_ID('TESTING')
All the above four methods will show you the script of the procedure names TESTING.
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
Hi Pinal, I didn’t know about OBJECT_DEFINITION, and we also could use:
SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID(‘TESTING’)
The replacement for sys.syscomments.
Correct Brunno!
Methods 2 & 3 seem to have problems with really large stored procedures.
district_id district_name Population
1 A 1000
2 B 2000
3 C 500
4 A 1500
5 A 2000
6 A 600
procedure with district name as input parameter and rank as other input parameter. Output of that procedure should be as below
if for that procedure district name A and rank 2 are passed as input parameters then output of that procedure should get the 2nd most populated district with name as “A”.
A 1600