8 thoughts on “SQL SERVER – Simple Use of Cursor to Print All Stored Procedures of Database Including Schema”

1. what about procedures which are encrypted?

   Reply

2. 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

   Reply

3. 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

   Reply

4. Hi Pinal & Chaitanya

   The word “Text” appears before the beginning of every stored procedure, Please advice how can that be eliminated.

   Thanks
   Manish

   Reply

5. 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

   Reply

6. Pingback: SQL SERVER – Using RANKING Functions Instead of SQL Looping Logic of Cursor – Quiz – Puzzle – 8 of 31 « SQL Server Journey with SQL Authority

7. 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.

   Reply

8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #004 « SQL Server Journey with SQL Authority