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)

13 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

    Like

  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

    Like

  3. Hi Pinal & Chaitanya

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

    Thanks
    Manish

    Like

  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

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

    Like

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

  8. hi. i have question.i need a store procedure Code.

    how we can gives a amount to system and system auto and soberly divide that amount to a some field until that amount finish . the first one i want which the system compare fields B with A when amount of filed B not abreast with field A , the system must deduct that amount which we gives to system and add to field B until be abreast with field A.
    like we give 13,800$ to system and system must auto do it.
    i have example for better understand.

    i have a depot database in SQL server and work with c# program.
    i have customer table which show records of customer which buy items (non cash) from us ,now i fill some record for Customer A like this.

    names of fields {customer id , customer name, name items, total price item, payment, date}
    first record: { 1, jan, books, 300$ , 100$ , 2015.1.1}
    second record: { 1, jan, computer, 2000$ , 1000$ , 2015.1.4}
    3 third record :{ 1, jan, chair, 500$, 100$, 2015.1.10 }
    4th record : { 1, jan, phone , 3000$, 1000$, 2015.1.20}

    now we a know Account of Customer A is 3000+2000+500+3000=13,000$
    he paid just 2,200$ of 13,000$ and we 13,000$-2,200$=10,800$ he must pay.

    now customer A come and pay 10,800$ . and we must describe and update this price to payment field until each fields of each record be abreast with total price item.
    for example : first record {total price item is 300$ and payment is 100$ which we must add 200$ which be abreast with total price item field we must softened of 10,800$. similar for all of record until all payment field be abreast with total price item.

    i need store procedure.
    thanks .

    Like

    • Seyed, Sorry, but I don’t have enough time right now to write code for you.
      BTW, the design of the table is incorrect. it would make things more complex. You should have ItemMaster, CustomerMaster and CustomerTransaction kind of table for managing such things easily.

      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