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)
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
Hi!
How to write a cursor stored procedure for sysobjects and syscolumns
I want to know, how a cursor stored procedure prints the select query as sp_help (table name) using sysobjects and syscolumns for the example given below
for example:
Select Id from sysobjects where name=’samp’
ans:503672842
select name from syscolumns where id=503672842
[...] INTO … SELECT instead of Cursor Simple Use of Cursor to Print All Stored Procedures of Database Simple Use of Cursor to Print All Stored Procedures of Database Including Schema Simple Example of Cursor Cursor to Kill All Process in [...]
hi,
Am beginner in sql server, i faced one question in interview,
i.e),
In my stored procedure i have a cursor but it killing the performance, can i use alternative for that? if it possible how can I ? with small example.. in simple language.
Thank full to you.
[...] Simple Use of Cursor to Print All Stored Procedures of Database Including Schema This is a very interesting requirement I used to face in my early career days, I needed to print all the Stored procedures of my database. Interesting enough I had written a cursor to do so. Today when I look back at this stored procedure, I believe there will be a much cleaner way to do the same task, however, I still use this SP quite often when I have to document all the stored procedures of my database. [...]