Presents your  
SQL SERVER E-NEWSLETTER for August 26, 2003 
 
<-------------------------------------------> 
  
RESURRECT FORGOTTEN STORED PROCEDURES WITH SP_STORED_PROCEDURES AND 
SP_HELPTEXT
  
When you need to locate and use a seldom-run stored procedure, you'll 
find that you can't remember all the stored procedures in your databases. 
This is especially true if you're looking for a stored procedure that
you only use once every three to six months.   
  
To get a list of all the stored procedures in your database, use 
sp_stored_procedures and then run sp_helptext to view the procedure
source code and find any needed parameters.   
  
Sp_stored_procedures returns a list of all the stored procedures in the 
requested environment. In the example below, we'll see what stored 
procedures are in the pubs database.   
  
USE pubs   
Exec sp_stored_procedures   
GO   
  
The first three columns of your result set should look like this:   
  
PROCEDURE_QUALIFIER     PROCEDURE_OWNER     PROCEDURE_NAME   
-------------------     ---------------      --------------   
pubs                    dbo                 authorpub;1   
pubs                    dbo                 byroyalty;1   
  
(Most of the other columns returned in the result set are "reserved for 
future use" so don't worry about them.)   
  
After getting the list back, you think authorpub (ignore the semi-colon 
and number 1) was the stored procedure you were looking for. Perhaps 
you need a little more information about authorpub to find out, so let's 
run

sp_helptext to look at its code.   
  
USE pubs   
exec sp_helptext 'authorpub'   
GO   
  
Look at the results--it's the entire script for the stored procedure.   
  
CREATE   procedure dbo.authorpub   
--declare procedure variables   
@ALName varchar(40) --input parameter   
AS   
--Assign columns to output and check for aauthor name   
SELECT a.au_lname, author = a.au_id, [Publisher] = p.pub_name   
FROM  Publishers p CROSS JOIN authors a   
WHERE a.au_lname LIKE @ALName   
  
Now you remember that this procedure returns the publisher from one 
table and the author ID from another table for the @ALName parameter or 
author's last name. So we'll run the stored procedure looking for the
author ID and publishers for a writer named Smith.   
  
USE pubs   
EXEC authorpub @ALName = 'Smith'   
GO   
  
The result set should return eight rows of publisher information.   
  
au_lname         author            Publisher             
--------         ------            --------------------   
Smith            341-22-1782       New Moon Books   
Smith            341-22-1782       Binnet & Hardley   
Smith            341-22-1782       Algodata Infosystems   
Smith            341-22-1782       Five Lakes Publishing   
Smith            341-22-1782       Ramona Publishers   
Smith            341-22-1782       GGG&G   
Smith            341-22-1782       Scootney Books   
Smith            341-22-1782       Lucerne Publishing   
  
(8 row(s) affected)   
  
J.E. Harvey, MCSD, MCDBA, has been tinkering with and writing about 
technology for more than two decades.   
  
----------------------------------------