[posted and mailed, please reply in news]
Jason Madison (ja***********@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,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
