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)
11 Comments. Leave new
what about procedures which are encrypted?
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
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
Hi Pinal & Chaitanya
The word “Text” appears before the beginning of every stored procedure, Please advice how can that be eliminated.
Thanks
Manish
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
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.
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
@Manish – Could you please share some sample?
AND is_ms_shipped 1
syntax correction pls
AND is_ms_shipped =1
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 .
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.