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 (https://blog.sqlauthority.com)

Database, SQL Scripts, SQL Stored Procedure
Previous Post
SQLAuthority News – SQL Server White Paper: SQL Server 2008 Compliance Guide
Next Post
SQL SERVER – 2008 – Download and Install Sample Database AdventureWorks 2008

Related Posts

11 Comments. Leave new

  • what about procedures which are encrypted?

    Reply
  • 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

    Reply
  • 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

    Reply
  • Hi Pinal & Chaitanya

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

    Thanks
    Manish

    Reply
  • 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

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

    Reply
    • Hi Santosh,

      You can use xml in stored procedure in place of cursor. it will not kill your performance.

      I think this is helpful for you.

      Thanks

      Reply
  • AND is_ms_shipped 1
    syntax correction pls
    AND is_ms_shipped =1

    Reply
  • seyed jafar
    May 27, 2015 4:42 pm

    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 .

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

      Reply

Leave a Reply

Menu
Exit mobile version