|
|
|
|
home > code > classic asp apps > SQLServerTools Object |
|
SQLServerTools ObjectThis one's like a double whammie! Welcome to the world of hard core SQL Server stored procedures and to theNextRecordset method of ADO's
recordset object... Before we go on, this requires SQL Server. WON'T
WORK WITH ACCESS.
That said, the goal for this example was easy... Get a list of all current tables in the database created by your's truly, get a list of dependent stored procedures for each table and do it all in a stored procedure! Easy... There are basically 2 parts to this example. The calling application (a VBScript class called SQLServerTools)
and the SQL Server stored
procedure: sp_Admin_ExampleTablesDependencies
(included with the class).
The VBScript class is easy and contains only 1 method. That method is
a great example of how to use the NextRecordset
method of ADO to capture
a second (or 50th) recordset returned by a procedure. For example, did you
know this was valid SQL:
"SELECT * FROM table1;SELECT * FROM table2;"
Well it is, SQL Server knows that this statement should produce two separate and distinct recordsets however if you execute that and don't use NextRecordset, you
will only get that first RS back. The stored procedure I wrote to do all the
work is the second part of the example. That procedure returns (2 * num of tables) recordsets
which is variable and based on the number of tables found in
sysobjects for your
particular db. Bottom line, that procedure always returns more than 1 recordset.
Right now, as you read this, I estimate that the procedure returned as many as 30 recordsets that were looped through with NextRecordset...
But that's not the real magic. The class only exists
to call the procedure and work with it's results. The stored procedure
itself does all the work.
I've commented both the app and the stored procedure to the max but I'll give you an overview of the stored procedure... The stored procedure uses a cursor to loop through the records of a recordset returned by the system table: sysobjects.
The process used is almost the same as using ADO to manipulate a recordset but right in the db
itself... this means huge speed bonus and, by writing a stored procedure, all that
logic is forever contained within and isn't spilled out into the class where it doesn't
belong.
So anyways, while the procedure is looping through each table returned by sysobjects,
it's simultaneously calling the system stored procedure:
sp_depends.
sp_depends is
a handy function that returns a recordset of names representing
every stored procedure that references
the entered table! sp_depends
can be used on any object, not just tables, but that is another
discussion for another day...
The output of sp_Admin_ExampleTablesDependencies
is below: Every table used in ASP
Emporium examples and the stored procedures that work with those tables...
Pretty slick, eh? and only the tip of the iceburg...
|
|||||||||||||||||||||||||||||||||||||||||||
