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 (http://blog.SQLAuthority.com)
pinaldave
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:
http://www.sqlservercurry.com/2008/05/find-out-all-views-in-database-using.html
http://www.sqlservercurry.com/2008/05/find-all-user-defined-functions-in.html
[...] by pinaldave 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 [...]
How about one for all views (instead of procedures)?