SPROCS
-
Hello All
I hope that everyone is having a wonderful friday.
How can I retreive all verbiage and information about each and every SPROC that is in a certain DB? I need all code from the SPROC itself. This is for documentation purposes. I would like to query this out, as opposed to opening each and every SPROC one at a time to copy and paste. There are a few hundred procs.
Thanks in advance for your help
Andrew
-
this should do it for you
exec dbo.sp_MSforeachdb @command1="select * from ?.INFORMATION_SCHEMA.ROUTINES"
information_schema.routines.routine_definition is a useful col as it contains the text of the SP
HTH
cheers
dbgeezer
-
Is it safe to assume that you have access to Enterprise Manager? If so, expand the SQL Server group, and then expand the server you wish to document. Expand databases. Expand the database you wish to document. Click on stored procedures. Select the set of stored procs. Right click the selection. Choose all tasks, generate SQL Script from the popup menu. Try a few different boxes on the tabs until you find a combination that meets your needs. My personal preference is to generate OEM text into a single file most of the time. Repeat the process for each server/database/stored proc that you need to document.
You can achieve similar result through query analyzer if you need to, but this is a reasonably fast method.
Hope this helps,
Wayne
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply