|
Viewing the stored procedures in a databaseIt'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).
Here's how
it looks when called:
CREATE PROCEDURE sp_GetAllSlides
AS
SELECT
pageNumber
FROM
slideShowPresentation
CREATE PROCEDURE sp_getAllUsrAddr
(
@mUsr VarChar(255)
)
AS
SELECT
ID, usr, firstName, lastName, streetAddress1, streetAddress2,
city, state, zip, eMailAddress, phone, fax, cell, notes
FROM
dayPlannerAddresses
WHERE
usr = @mUsr
ORDER BY
lastName
CREATE PROCEDURE sp_GetMailRecips
AS
SELECT
emailAddress, ID
FROM
autoResponder
|
