顯示資料庫中的預存程序

來源:互聯網
上載者:User
預存程序|資料|資料庫|顯示 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 ");



相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。