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 (https://blog.sqlauthority.com)
11 Comments. Leave new
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
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
I wrote a script some time back to show all the Views and UDF’s using different techniques:
How about one for all views (instead of procedures)?
Use
WHERE type = ‘V’
instead of
WHERE type = ‘P’
Hi,
This articles was very helpful.
Really Helpful Script
That was actually another really helpful script, thanks Pinal. How much of your time do you spend in SQL??!
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.
this is a very helpful script and also a good example of cursor..
Thanks Pinal..
If I change the P to V for views. How can I output the results of the “Create View” queries into text files, without actually replacing the views that I already have out there. I am wanting to export all of the Select statements into text files, then when I search for a magic subquery I wrote a long time ago, I can search thru *.txt for “(Select” and look through those results for a magic subquery code that I can reuse with modifications.
When I run the above with “V” in the where clause it works, but the Grid columns width is onlhy as wide as the name of the view, and I would like to force it to fit the width of the screen instead or just a number like 256