显示数据库中的存储过程

March 25,2004

It's no easy trick to see stored procedures in a database programmatically with a scripting language like ASP. If you're using MS Access, you're out of luck. Access provides no way to see the actual meat of a stored procedure although you can get the names of the procedures in the database with the ADOX.Catalog COM object.

But, if you are using SQL Server (like you should be because you care about your data), you have a guaranteed way to view all your stored procedures using two globally-available system objects: the built-in sysobjects system table and the sp_helptext system stored procedure.

With a couple of simple loops, everything about your stored procedures can be viewed and accessed programmatically in just a few lines. Here's the results of the function (I'm allowing you to view the first few procedures only because this method can be pretty resource-intensive. If you want the complete list of procedures I use on this site, you can get it here.) Here's how it looks when called:

CREATE PROCEDURE sp_addAdvertLink
(
@m1 DateTime,
@m2 DateTime,
@m3 VarChar(20),
@m4 VarChar(20),
@m5 VarChar(255),
@m6 VarChar(255),
@m7 VarChar(255),
@m8 VarChar(255)
)
AS
INSERT INTO
easyAds
(
display_date, display_time, display_month, display_day,
usr_ip_address, usr_browser, display_adName, usr_referer
)
VALUES
(
@m1, @m2, @m3, @m4, @m5, @m6, @m7, @m8
)

CREATE PROCEDURE sp_AddMailRecip
(
@mIPAddr VarChar(255),
@mEmailAddr VarChar(255)
)
AS
INSERT INTO
autoResponder
(
IPaddress, emailAddress
)
VALUES
(
@mIPAddr, @mEmailAddr
)

CREATE PROCEDURE sp_addUsrAddr
(
@mUsr VarChar(255),
@mFstNme VarChar(255),
@mLastNme VarChar(255),
@mAddr1 VarChar(255),
@mAddr2 VarChar(255),
@mcity VarChar(255),
@mstate VarChar(255),
@mzip VarChar(255),
@mEmail VarChar(255),
@mphone VarChar(255),
@mfax VarChar(255),
@mcell VarChar(255),
@mnotes Text
)
AS
INSERT INTO
dayPlannerAddresses
(
usr, firstname, lastname, streetAddress1, streetAddress2,
city, state, zip, eMailAddress, phone, fax, cell, notes
)
VALUES
(
@mUsr, @mFstNme, @mLastNme, @mAddr1, @mAddr2, @mcity, @mstate,
@mzip, @mEmail, @mphone, @mfax, @mcell, @mnotes
)

------------------sysobjects.asp-------------源程序--------------

<% @ Language = JScript %>
<%
with (Response) {
Buffer = true;
Expires = 0;
Clear();
}

function ShowProcs() {
//set-up database connection information
var ConnString = Application("dbConn");
var ConnUser = Application("dbUsr");
var ConnPass = Application("dbPass");

//set this next variable to false to unrestrict the system
var LimitResults = true;
var MagicNumber = 2;

//get a connection
var c = new ActiveXObject("ADODB.Connection");

//open database
c.Open(ConnString, ConnUser, ConnPass);

//enable error-trapping
try {

//attempt to access the sysobjects table.
//if you try this with MS Access, you will get an error...

//sysobjects table contains information about everything
//in your database. From tables to views, and whatever in
//between, all that stuff is in the sysobjects table.

//in my db, a status of 24 indicates that it's a procedure
//that I added and not one of the other bizarre stored procedures
//that were mixed in there as well. A type of P indicates Stored Procedure.
//Other values for type can be 'U' for user tables, 'R' for rule,
//'s' for system tables (like sysobjects), 'TR' for triggers, 'V' for view, //etc... In this case 'P' is the one we want.
var p = c.Execute("SELECT Name FROM sysobjects WHERE status = 24 AND type = 'P' ORDER BY Name;");
} catch(e) {

//oops - sysobjects table not found. You must be using MS Access.
//Or you forgot to re-code the connection string.
Response.Write("This example only works with <B>SQL Server");
Response.Write("</B>. "sysobjects" table does not exist!<BR><BR>");
Response.Write("If you are using SQL server, you may need to ");
Response.Write("adjust the ConnString, ConnUser and ConnPass variables ");
Response.Write("in the ShowProcs( ) procedure to reflect your database's ");
Response.Write("valid connection string and user account information.");

//close database connection because we're leaving...
c.Close();
c = null;

//quit procedure...
return;
}

//if we get here, we're in the SysObjects table and ready to go.
if (LimitResults) {
var i = 0;
}
while (!p.BOF && !p.EOF) {

//call the system stored procedure "helptext" which will return
//the exact text of the stored procedure, as entered by you...
//as a multiple recordset consisting of one field in each row.
//The name of the field is "text" and it's datatype is nVarChar(255).
//Each row is the equivalent of each line of the procedure as you
//entered it. For example, a procedure like this:
// CREATE PROCEDURE sp_getitall
// AS SELECT * FROM Table
//would return two rows when gathered with sp_helptext.
var r = c.Execute("EXEC sp_helptext '" + p.Fields(0).value + "'");

//check to make sure there is a record. Theoretically there
//has to be at least 1 record returned since sysobjects will
//always return a "good" procedure name and not just some
//random stored procedure name.
if (!r.BOF) {

//move to the top of the procedure's text...
r.MoveFirst();
while (!r.BOF && !r.EOF) {

//return the procedure's text...
//one line at a time.
//r.Fields(0).value is also equivalent
//to the line below:
//  Response.Write(r("Text") + "<BR>");
Response.Write("<CODE STYLE="font-size:9pt;font-
family:helvetica;">" + r.Fields(0).value + "</CODE><BR>");

//move to the next line
r.MoveNext();
}
}

//close the sp_helptext generated recordset
r.Close();
r = null;

//move to the next sysobject (in this case, the next
//stored procedure)
p.MoveNext();
Response.Write("<BR>");
if (LimitResults) {i++}
if (LimitResults) {if (i > MagicNumber) {break;}}
}

//close the sysobject recordset
p.Close();
p = null;

//close the db connection
c.Close();
c = null;
}
%>

<html>
<head>
  <title>The ASP Emporium - JScript: Viewing the stored procedures in a database</title>
  <style type="text/css">
  h3 {color: #CC3300;}
  </style>
</head>
<body background="/aspEmporium/pix/bg.gif" bgcolor="#EEEEEE">
<table width=100% cellpadding=0 cellspacing=0 border=0>
<tr>
  <td width=50% valign=top align=left>
  <img width=283 height=36 border=0 src="/aspEmporium/pix/emporium.gif"
   border=0 alt="The ASP Emporium">
  <br>
  <font face=verdana size=-2 color=#CC3300>
  <img width=438 height=25 border=0 src="/aspEmporium/pix/blurb.gif"
   alt="Free Active Server Applications and Examples by Bill Gearhart">
  </font>
  </td>
  <td width=50% valign=top align=right>
  <font size=-1 face=arial>
  <img width=197 height=30 border=0 src="/aspEmporium/pix/online.gif"
   alt="Online since Friday January 7, 2000"><br>
  </font>
  </td>
</tr>
</table>
<br>
<table width=100% cellpadding=1 cellspacing=1 border=0 bgcolor=#60786B>
<tr>
  <td bgcolor=#60786B width="20%">
  <img width=195 height=20 border=0
   src="/aspEmporium/pix/location.gif" alt="">
  </td>
  <td bgcolor=#FFFFEE width="80%">
  <font size=-1 color=#60786B face=arial>

  </font>
  </td>
</tr>
</table>
<br>
  <table width=100% cellpadding=2 cellspacing=0 border=0>
  <tr>
<td valign=top></td>
<td valign=top>
<font face="arial, verdana, helvetiva, times new roman">

<H3>Viewing the stored procedures in a database</H3>

It's no easy trick to see stored procedures in a database
programmatically with a scripting language like ASP. If you're
using MS Access, you're out of luck. Access provides no way to
see the actual meat of a stored procedure although you can get
the <A HREF="/aspEmporium/codelib/procs.htm">names of the procedures</A>
in the database with the <CODE>ADOX.Catalog</CODE> COM object.
<BR>
<BR>
But, if you are using SQL Server (like you should be because you
care about your data), you have a guaranteed way to view all your
stored procedures using two globally-available system objects:
the built-in <CODE>sysobjects</CODE> system table and the
<CODE>sp_helptext</CODE> system stored procedure.
<BR>
<BR>
With a couple of simple loops, everything about your stored procedures
can be viewed and accessed programmatically in just a few lines. Here's
the results of the function (I'm allowing you to view the first few
procedures only because this method can be pretty resource-intensive. If
you want the complete list of procedures I use on this site, you can
<A HREF="/aspEmporium/downloads/sql.txt">get it here</A>.) Here's how
it looks when called:
<BR>
<BR>
<BR>

<%  ShowProcs();  %>

<BR>
<BR>

</font>
</td>
  </tr>
  </table>
</body>
</html>