SQL SERVER – Simple Use of Cursor to Print All Stored Procedures of Database

SQLAuthority Blog reader YordanGeorgiev has submitted very interesting SP, which uses cursor to generate text of all the Stored Procedure of current Database. This task can be done many ways, however, this is also interesting method.

USE AdventureWorks
GO
DECLARE @procName VARCHAR(100)
DECLARE @getprocName CURSOR
SET
@getprocName = CURSOR FOR
SELECT
s.name
FROM sysobjects s
WHERE type = 'P'
OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC
sp_HelpText @procName
FETCH NEXT
FROM @getprocName INTO @procName
END
CLOSE
@getprocName
DEALLOCATE @getprocName
GO

Just give this script a try and it will print text of all the SP in your database. If you are using Grid View for Result Pan I suggest to change it to Text View (CTRL+T) to read the text easily.

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Cursor, SQL Scripts, SQL Stored Procedure
Previous Post
SQLAuthority News – Author Visit – South Asia MVP Open Day 2008 – Goa – Day 3
Next Post
SQLAuthority News – SQL Server White Paper: SQL Server 2008 Compliance Guide

Related Posts

11 Comments. Leave new

  • Thanks for the script, however I did notice it won’t work with Schema-objects (such as in AdventureWorks2008)

    Here’s my modified script (thanks to tracing SSMS as well)
    It essentially does below in AdventureWorks2008

    sp_HelpText [dbo].[uspGetManagerEmployees]
    sp_HelpText [dbo].[uspGetWhereUsedProductID]
    sp_HelpText [dbo].[uspPrintError]
    sp_HelpText [HumanResources].[uspUpdateEmployeeHireInfo]
    sp_HelpText [dbo].[uspLogError]
    sp_HelpText [HumanResources].[uspUpdateEmployeeLogin]
    sp_HelpText [HumanResources].[uspUpdateEmployeePersonalInfo]
    sp_HelpText [dbo].[uspSearchCandidateResumes]
    sp_HelpText [dbo].[uspGetBillOfMaterials]
    sp_HelpText [dbo].[uspGetEmployeeManagers]

    — Jerry Hung
    DECLARE @procName VARCHAR(100)
    DECLARE @getprocName CURSOR
    SET @getprocName = CURSOR FOR
    –SELECT s.name FROM sysobjects s WHERE TYPE = ‘P’
    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
  • Hi Pinal

    thanks for your Script.

    When I copy and paste your various sample codes, i have always a problem with the quotation marks that you are using.
    I´m forced to seek and replace them all.
    For example

    WHERE type = ‘P’ — doesn´t work

    WHERE type = ‘P’ — OK

    Maybe you could change that in your editor settings.
    Christoph

    Reply
  • I wrote a script some time back to show all the Views and UDF’s using different techniques:

    Reply
  • Hi,

    This articles was very helpful.

    Reply
  • Varinder Sandhu
    May 21, 2011 12:33 pm

    Really Helpful Script

    Reply
  • That was actually another really helpful script, thanks Pinal. How much of your time do you spend in SQL??!

    Reply
  • Thanks, that saved me a fair bit of time. How much of your time have you spent ninja-ing SQL Server? You sound like an epic ninja at it!

    Thanks again, Garry.

    Reply
  • this is a very helpful script and also a good example of cursor..
    Thanks Pinal..

    Reply
  • If I change the P to V for views. How can I output the results of the “Create View” queries into text files, without actually replacing the views that I already have out there. I am wanting to export all of the Select statements into text files, then when I search for a magic subquery I wrote a long time ago, I can search thru *.txt for “(Select” and look through those results for a magic subquery code that I can reuse with modifications.
    When I run the above with “V” in the where clause it works, but the Grid columns width is onlhy as wide as the name of the view, and I would like to force it to fit the width of the screen instead or just a number like 256

    Reply

Leave a Reply