顯示數(shù)據(jù)庫中的存儲過程

發(fā)布時間：2008-07-11 閱讀數(shù)：次來源：網(wǎng)樂原科技

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>