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

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)

About these ads

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

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

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

  3. Hi Pinal & Chaitanya

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

    Thanks
    Manish

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

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

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #004 « 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