Displaying stored procedures in a database

Stored Procedures | data | database | display
It's no easy trick to the stored procedures in a database programmatically with a scripting-like ASP. If you are using MS Access, you are ' re out of luck. Access provides no way to the actual meat of a stored procedure although you can get the names of the procedures in th e database with the ADOX. Catalog COM object.

But, if you are are using SQL Server (like you should is because you care about your data), and 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_he Lptext 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 ' the ' the ' the ' the ' the ' the ' the ' the ' few procedures only because this Ty Resource-intensive. If you are want the complete list of procedures I use on this site and you can have 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-------------Source program--------------

<% @ 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'll get a error ...

Sysobjects table contains information about everything
In your database. From the tables to views, and whatever in
Between, all of that stuff are in the sysobjects table.

In I db, a status of indicates that it ' s a procedure
That I added and isn't one of the other bizarre stored procedures
That's were mixed in there as. A type of P indicates Stored Procedure.
Other values for type can is ' 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 = on and type = ' ORDER by Name;");
catch (e) {

Oops-sysobjects table not found. You must is using MS Access.
Or you forgot to re-code the connection string.
Response.Write ("This example in works with <b>sql Server");
Response.Write ("&LT;/B&GT; \ "Sysobjects\" table does not exist!<br><br> ");
Response.Write ("If you are using SQL Server, with may need to");
Response.Write ("Adjust the connstring, Connus