|
|
|
|
|
We can now get into the main script.
This script again uses a nested cursor. Therefore you will see two variables
@fetch_outer and @fetch_inner defined just as we did in the sp__loaddata script.
@fecth_outer is used to loop over all of the tables within a database. @fetch_inner
is used to loop over the referenced tables. We start off by creating three temporary
tables: #fkeys, #constraints, and #pivot. #fkeys will contain the raw reference data
that will be gathered from sysreferences. #constraints will contain the primary key
to foreign key relationship in human readable column name and table name form.
#pivot is a work table that will be used to drive a portion of the script and will be
covered in detail below. Before getting into why this table is here, we need to explain some concepts. A table pivot is where you take a long, narrow table and turn it into a short, wide table. (Think of this as a cross tab.) Folding a table is the exact opposite where you take a short, wide table and turn it into a long narrow table. We will be performing a table fold on the sysreferences table. Why? Because we need to access data in all of the columns and the foreign key could be 16 columns wide. In order to do this we would either need a 32 table join (join sysreferences to itself 32 times) or 32 insert statements into a temporary table. Both of these are inefficient. We are going to use a much better method that will perform the fold in a single SQL statement. In order to do this, we need the #pivot table. This is used as nothing more than a selector as will be explained below. Because we have 16 columns that we need data from, we put in 16 rows numbered 1 - 16. (This can be adapted to accomodate any number of columns and any range of data.) We are now ready to proceed with the script. The first thing that is done is to grab all of the tables in the database into a cursor. It is important to note the presence of the for read only clause. A cursor by default is updateable. I only want to read data, so I make sure that I am protected from any inadvertant modifications. In fact I recommend always explicitly specifying for update or for read only. We then fetch the first table and save @@fetch_status into a local variable and then enter the loop for subsequent processing of the outer cursor. We use this table name to get the object ID and feed that as a parameter to the inner cursor. This inner cursor will contain the set of rows that have this table as the parent side of the foreign key relationship. We do the initial fetch and save @@fetch_status into a local variable for subsequent processing of this loop. We then run head first into what looks like a bunch of gobbledygook! --Build the table for the set
of raw reference data (folds sysreferences for easy manipulation) What this SQL statement does is perform the table fold we outlined earlier. It turns sysreferences into a table that looks like the following: create table #fkeys( pkdb_id int
not null, |
All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.