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. heres the results of the function (im 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.) heres 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 its 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 were leaving...
c.close();
c = null;
//quit procedure...
return;
}
//if we get here, were 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 its 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 procedures text...
r.movefirst();
while (!r.bof && !r.eof) {
//return the procedures 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>
<!--#include virtual = "/aspemporium/inc/quicknav3.asp"-->
</font>
</td>
</tr>
</table>
<br>
<table width=100% cellpadding=2 cellspacing=0 border=0>
<tr>
<td valign=top><!--#include virtual = "/aspemporium/inc/sidemenu_js.asp"--></td>
<td valign=top>
<font face="arial, verdana, helvetiva, times new roman">
<h3>viewing the stored procedures in a database</h3>
its no easy trick to see stored procedures in a database
programmatically with a scripting language like asp. if youre
using ms access, youre 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. heres
the results of the function (im 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>.) heres how
it looks when called:
<br>
<br>
<br>
<% showprocs(); %>
<br>
<br>
<!--#include virtual = "/aspemporium/inc/jsexampleoptions.asp"-->
</font>
</td>
</tr>
</table>
</body>
</html>
文章整理：站长天空 网址：http://www.z6688.com/
以上信息与文章正文是不可分割的一部分,如果您要转载本文章,请保留以上信息，谢谢!
