Generating Foreign Key Script

We sometimes get very large databases that we want to cut down to use for
testing.

The information is all related to a central accounts table.

The way I thought of doing this is to grab all the foreign constraints and
turn them into cascade delete constraints, then delete as many accounts as I
want.

After this I will restore the constraints back to their original state.

I am having a problem doing this as I cannot find a way to programatically
get the add constraint foreign key sql.

For example, I can use sysforeignkeys to list all the foreign keys or
ADO.OpenSchema(adSchemaForeignKeys...) but this doesn't give me the actual
SQL to modify.

My idea was to go through the database saying:

alter table (tablename) drop constraint (foreign key)
alter table (tablename) add constraint (foreign key) on delete cascade

delete various accounts and related data

alter table (tablename) drop constraint (foreign key)
alter table (tablename) add constraint (foreign key)

Can anyone assist?

Thanks[posted and mailed, please reply in news]

Jason Madison (jason.madison@.btinternet.com) writes:
> We sometimes get very large databases that we want to cut down to use for
> testing.
> The information is all related to a central accounts table.
> The way I thought of doing this is to grab all the foreign constraints
> and turn them into cascade delete constraints, then delete as many
> accounts as I want.

Here is some code that I have lying around. It does not exactly do what
you are asking for - it is actually moving the fkeys to refer from a
previous generation a table to a new, but you should be able to work
from it.

It is actually part of a Perl script, which is why all @. are preceded
by \. Just remove these. There are also things that start with $. These
are Perl variables that are set in the beginning of the snippet.

# Determine parameters that depends on the SQL Server version.
if (version_ge($X->{SQL_version}, '7')) {
$vclen = 4000;
$varchar = 'nvarchar';
$maxlen = 127; # Max len for an object name.
$quotef = 'quotename'; # Function to quote a name.
$lenf = 'len'; # Function to get string length.
}
else {
die "The SQL generated by aba_tblfkey2 does not run on SQL 6.5\n";
}

# Stringify table names
$old_table = sql_string($old_table);
$this_table = sql_string($this_table);

$sql = <<SQLEND;
-- Move foreign keys from $old_table to $this_table.
SET NOCOUNT ON

DECLARE \@.dummy tinyint,
\@.fktbl sysname,
\@.fkcol sysname,
\@.refcol sysname,
\@.keyno smallint,
\@.constr sysname,
\@.prev_constr sysname,
\@.drop $varchar($vclen),
\@.add1 $varchar($vclen),
\@.add2 $varchar($vclen),
\@.tmpname sysname,
\@.no smallint,
\@.err int

DECLARE sql_fkey_cur INSENSITIVE CURSOR FOR
SELECT k = 1, constr = object_name(constid), fktbl = object_name(fkeyid),
fkcol = col_name(fkeyid, fkey), refcol = col_name(rkeyid, rkey),
keyno
FROM sysforeignkeys
WHERE rkeyid = object_id($old_table)
UNION
SELECT k = 2, NULL, NULL, NULL, NULL, NULL
ORDER BY k, constr, keyno
OPEN sql_fkey_cur

SELECT \@.no = 0
WHILE 1 = 1
BEGIN
FETCH sql_fkey_cur INTO \@.dummy, \@.constr, \@.fktbl, \@.fkcol, \@.refcol,
\@.keyno
IF \@.\@.fetch_status <> 0
BREAK

IF (\@.constr <> \@.prev_constr OR \@.constr IS NULL) AND
\@.prev_constr IS NOT NULL
BEGIN
-- Add the new foreign constraint.
PRINT \@.add1
PRINT \@.add2
EXECUTE(\@.add1 + \@.add2 + ')')
IF \@.\@.error <> 0 BREAK

-- Drop the old reference, once the new is in place. If we drop first,
-- and something goes wrong, we would lose information.
PRINT \@.drop
EXECUTE(\@.drop)
IF \@.\@.error <> 0 BREAK

-- Rename the new reference to the real name.
EXEC \@.err = sp_rename \@.tmpname, \@.prev_constr
SELECT \@.err = coalesce(nullif(\@.\@.error, 0), \@.err)
IF \@.err <> 0 BREAK
END

IF \@.keyno = 1
BEGIN
-- Set up command to create new reference under a temporary name. Use
-- place holders for the column lists.
SELECT \@.tmpname = 'new_' + ltrim(str(\@.no))
SELECT \@.tmpname = \@.tmpname +
substring(\@.constr, 1, $maxlen - $lenf(\@.tmpname))
SELECT \@.no = \@.no + 1
SELECT \@.add1 = 'ALTER TABLE ' + $quotef(\@.fktbl) +
' ADD CONSTRAINT ' + $quotef(\@.tmpname) +
' FOREIGN KEY (' + $quotef(\@.fkcol),
\@.add2 = ') REFERENCES ' + $quotef($this_table) + '(' +
$quotef(\@.refcol)

-- And set up command to drop current constraint.
SELECT \@.drop = 'ALTER TABLE ' + $quotef(\@.fktbl) +
' DROP CONSTRAINT ' + $quotef(\@.constr)
END
ELSE
BEGIN
-- Add more columns to \@.add command.
SELECT \@.add1 = \@.add1 + ', ' + $quotef(\@.fkcol),
\@.add2 = \@.add2 + ', ' + $quotef(\@.refcol)
END

SELECT \@.prev_constr = \@.constr
END

DEALLOCATE sql_fkey_cur
SQLEND

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp