Way to view all stored procedures

[Shinpei Kuga]

I have MS SQL Server 7 and over the years have built quite a few
Stored Procedures. It would be extremely convenient if I could print out
or view ALL the text of ALL the stored proceudres at once. Is there a way i
can do this? Is tehre a way I can make a quick print out?

[Jerry Boone]

I think this should do it...

SELECT name, type
FROM dbo.sysobjects
WHERE (type = 'P')

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access
"Shinpei Kuga" <sn***@mit.edu> wrote in message
news:2e**************************@posting.google.c om...

I have MS SQL Server 7 and over the years have built quite a few
Stored Procedures. It would be extremely convenient if I could print out
or view ALL the text of ALL the stored proceudres at once. Is there a way i can do this? Is tehre a way I can make a quick print out?

[Erland Sommarskog]

Shinpei Kuga (sn***@mit.edu) writes:

I have MS SQL Server 7 and over the years have built quite a few Stored
Procedures. It would be extremely convenient if I could print out or
view ALL the text of ALL the stored proceudres at once. Is there a way i
can do this? Is tehre a way I can make a quick print out?

In Enterprise Manager, find the database, select Stored Procedures.
Right-click and select All Tasks. In the submenu you find Generate
SQL Scripts.

Note: the instructions is from memory and for SQL2000, but I would guess
that the menus are the same in SQL7. In any case, SQL7 does have the
functionality.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Jerry Boone]

Whoops - misread your question,

Do what Erland recommends - generating sql scripts is a good way to do that.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access
"Jerry Boone" <je***@antech.biz.killspam> wrote in message
news:3P**********************@newssvr11.news.prodi gy.com...

I think this should do it...

SELECT name, type
FROM dbo.sysobjects
WHERE (type = 'P')

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access
"Shinpei Kuga" <sn***@mit.edu> wrote in message
news:2e**************************@posting.google.c om...

I have MS SQL Server 7 and over the years have built quite a few
Stored Procedures. It would be extremely convenient if I could print out
or view ALL the text of ALL the stored proceudres at once. Is there a

way i

can do this? Is tehre a way I can make a quick print out?

[jpatti]

The sample code provided simply provides a list of stored procedure names; it does not provide the code itself.

Here is how to print-out all your stored procedure code:

1. Connect via SQL Enterprise Manager as a user with administrative rights to the database and expand the database.
2. Click on "stored procedures" in the left pane and you will see in the right pane the stored procedures used for this application (excluding those with names dt_*, which are part of SQL server itself).
3. Right-click on any stored procedure in the right pane and from the resulting menu, choose All Task -> Generate SQL Script.
4. In the wizard that popsup, you are in the "General" tab. Click the "Show All" button, then put a check next to "All stored procedures".
5. Continuing with the popup, next click the "formatting" tab and uncheck all the checkboxes there except the "Generate the CREATE <object> command for each object" checkbox.
6. No need to make changes on the "Options" tab. So just click "OK" and then choose a path and filename for the stored procedure print-out.
7. Open the .sql file in a text editor to view or print. If you ignore all the lines that say:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
..what remains is the text of all the stored procedures used in the database (excluding the dt* ones).

Please don't reply to me; I only joined the forum to answer this question. I Googled it, did not find a good answer, so experimented until I figured it out. Just wanted to pass the info along for the next person trying to figure this out.