I love active participation from my readers. Just a day ago I wrote article about SQL SERVER – Simple Use of Cursor to Print All Stored Procedures of Database. I just received comment from Jerry Hung who have improved on previously written article of generating text of Stored Procedure.
DECLARE @procName VARCHAR(100)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
SELECT Name = '[' + SCHEMA_NAME(SCHEMA_ID) + '].[' + Name + ']'
FROM sys.all_objects WHERE TYPE = 'P'
AND is_ms_shipped 1
OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ’sp_HelpText ’ + @procName
EXEC sp_HelpText @procName
FETCH NEXT FROM @getprocName INTO @procName
END
CLOSE @getprocName
DEALLOCATE @getprocName
GO
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
what about procedures which are encrypted?
There is a small mistake in the procedure written above.
I think this could help you out
DECLARE @procName VARCHAR(1000)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
SELECT Name = ‘[' + SCHEMA_NAME(SCHEMA_ID) + '].[' + Name + ']‘
FROM sys.all_objects WHERE TYPE = ‘P’
AND is_ms_shipped= 1
OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ’sp_HelpText ‘ + @procName
EXEC sp_HelpText @procName
FETCH NEXT FROM @getprocName INTO @procName
END
CLOSE @getprocName
DEALLOCATE @getprocName
GO
or else we can use
DECLARE @procName VARCHAR(1000)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
SELECT Name = ‘[' + SCHEMA_NAME(SCHEMA_ID) + '].[' + Name + ']‘
FROM sys.all_objects WHERE TYPE = ‘P’
AND is_ms_shipped<= 1–for all the procedures
OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ’sp_HelpText ‘ + @procName
EXEC sp_HelpText @procName
FETCH NEXT FROM @getprocName INTO @procName
END
CLOSE @getprocName
DEALLOCATE @getprocName
GO
Hi Pinal & Chaitanya
The word “Text” appears before the beginning of every stored procedure, Please advice how can that be eliminated.
Thanks
Manish