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 (http://blog.SQLAuthority.com)

13 thoughts on “SQL SERVER – Simple Use of Cursor to Print All Stored Procedures of Database

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

    Like

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

    Like

  3. Pingback: SQL SERVER - Simple Use of Cursor to Print All Stored Procedures of Database Including Schema Journey to SQL Authority with Pinal Dave

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

    Like

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

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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s