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)

About these ads

12 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

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