SQL SERVER – What are the Different Ways to Script Procedure with T-SQL

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)

Solarwinds
Previous Post
SQL SERVER – One Trick of Handling Dynamic SQL to Avoid SQL Injection Attack?
Next Post
Interview Question of the Week #006 – Is Shrinking Database Good or Bad?

Related Posts

5 Comments. Leave new

Leave a Reply

Menu