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. Here are the different ways to script procedure code, one by one.

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.

Which of the Different Ways to Script Procedure Code I Pick

All four methods show the code, but they do not behave the same with long procedures. Here is how I think about them:

• OBJECT_DEFINITION returns the full text as NVARCHAR(MAX), so it is my first choice. It also works for views, functions and triggers. sys.sql_modules has the same full text in its definition column.
• sp_helptext returns the code as many rows. It is easy to read, but very long lines can be split, so check before you copy it back.
• INFORMATION_SCHEMA.ROUTINES keeps only the first 4,000 characters, so a large procedure comes back cut off.
• sys.syscomments is an old compatibility view that stores the text in pieces across several rows. I avoid it in new scripts.

Two more tips. If a procedure was created WITH ENCRYPTION, none of these methods will show its text; you get NULL or a message that the text is encrypted. And if you get NULL for a procedure that is not encrypted, check your permissions, because you need VIEW DEFINITION on the object to see its code.

Finally, when you copy a long definition out of the SSMS grid, make sure nothing got trimmed along the way. Comparing the length with LEN(OBJECT_DEFINITION(OBJECT_ID('dbo.MyProc'))) is a quick way to be sure. If your copy is shorter, use the Generate Scripts option in SSMS for that procedure, which writes out the complete text.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.