Often peple ask this question in the forum. "I created a procedure with proper alignment. But when I wanted to alter the procedure, result of sp_helptext did not keep the original alignment. How do I keep the original format?"
Well. One answer is to make use of Script Stored Procedure --> ALTER TO option from Management Studio. If you want to use sp_helptext system procedure to view and alter, set the result mode of the query window to Text (Press CTRL+T). The text result mode will keep the original alignment
Consider this simple procedure
create procedure test as select name,create_date from sys.objects
Now set the result mode to Text and run this
EXEC sp_helptext test
The result will have the origninal formatting with proper alignment. So you need to aware of this when using sp_helptext system proceudre
Tags: t-sql, sql_server, sqlserver, tsql, sp_helptext,
You can also query sys.sql_modules, instead of using sp_helptext
sys.sql_modules
sp_helptext
Hi Marc will you please give example for your sys.sqlmodules I am using SQL 2008 R2 & failed to find sys.sqlmodules or way to execute it to retrieve stored procedure text
@Anand:
http://msdn.microsoft.com/en-us/library/ms175081(v=sql.105).aspx
SELECT [definition] from [sys].[sqlmodules] [sm] WHERE [sm].[objectid] = OBJECT_ID(N'foo')