Hi all,
This may be old news to some but I thought I would post this code in case it would be helpful to someone.
Basically the problem I had to deal with was this: we are developing a database application iteratively and every so often need to install a new version at the client site. As you would normally do, we have been using a script with "create table" statements that builds the database from scratch. In this script, preceding the create table statements, are "drop table" statements in the reverse order (so that tables referenced by foreign key constraints are dropped before the referencing tables). This works fine if the number and names of the tables doesn't change much. However, if I get rid of tables or change their names, the script won't work correctly the first time we use it because the old tables with the old names are still hanging around. Their foreign key constraints prevent some of the tables from being deleted and recreated, which screws up the install from that point forward, etc., etc. I originally was going to drop the entire database and recreate it, but this turned out to be difficult, though I don't really remember why -- maybe something having to do with it being a clustered server, and/or setting database options? Not sure.
Anyway, this is the solution I came up with. First of all, we have a naming convention such that all the tables that have to do with our application have an "r_" in front of the name. So, I had been thinking along the lines of dropping all the tables from sysobjects that have names starting with 'r_'. However, I wasn't sure what to do about the FK constraints until I checked out the "sysreferences" table. This table is essentially a join table that stores all the foreign key constraints. What you do is use the sysreferences table to find the "leaf nodes" in the DAG created by the foreign key references, and then keep dropping leaf nodes until you have none left! Here's the code:
declare @tablename nvarchar(500)
declare @sysid int
declare @sysid_tmp int
declare @sql nvarchar(100)
select top 1 @sysid = id from sysobjects where name like 'r_%' and type = 'U'
while (@sysid is not null)
begin
-- go down the DAG to the leaf node
set @sysid_tmp = null
select @sysid_tmp = fkeyid
from sysreferences
where rkeyid = @sysid
while (@sysid_tmp is not null)
begin
set @sysid = @sysid_tmp
set @sysid_tmp = null
select @sysid_tmp = fkeyid
from sysreferences
where rkeyid = @sysid
end
-- now drop the table
select @tablename = name from sysobjects where id = @sysid
set @sql = N'drop table ' + @tablename
exec sp_executesql @sql
set @sysid = null
select top 1 @sysid = id from sysobjects where name like 'r_%' and type = 'U'
end
P.S. This is SQL 2000. I think it would probably work in 7.0, but I don't know about 6.5.
This may be old news to some but I thought I would post this code in case it would be helpful to someone.
Basically the problem I had to deal with was this: we are developing a database application iteratively and every so often need to install a new version at the client site. As you would normally do, we have been using a script with "create table" statements that builds the database from scratch. In this script, preceding the create table statements, are "drop table" statements in the reverse order (so that tables referenced by foreign key constraints are dropped before the referencing tables). This works fine if the number and names of the tables doesn't change much. However, if I get rid of tables or change their names, the script won't work correctly the first time we use it because the old tables with the old names are still hanging around. Their foreign key constraints prevent some of the tables from being deleted and recreated, which screws up the install from that point forward, etc., etc. I originally was going to drop the entire database and recreate it, but this turned out to be difficult, though I don't really remember why -- maybe something having to do with it being a clustered server, and/or setting database options? Not sure.
Anyway, this is the solution I came up with. First of all, we have a naming convention such that all the tables that have to do with our application have an "r_" in front of the name. So, I had been thinking along the lines of dropping all the tables from sysobjects that have names starting with 'r_'. However, I wasn't sure what to do about the FK constraints until I checked out the "sysreferences" table. This table is essentially a join table that stores all the foreign key constraints. What you do is use the sysreferences table to find the "leaf nodes" in the DAG created by the foreign key references, and then keep dropping leaf nodes until you have none left! Here's the code:
declare @tablename nvarchar(500)
declare @sysid int
declare @sysid_tmp int
declare @sql nvarchar(100)
select top 1 @sysid = id from sysobjects where name like 'r_%' and type = 'U'
while (@sysid is not null)
begin
-- go down the DAG to the leaf node
set @sysid_tmp = null
select @sysid_tmp = fkeyid
from sysreferences
where rkeyid = @sysid
while (@sysid_tmp is not null)
begin
set @sysid = @sysid_tmp
set @sysid_tmp = null
select @sysid_tmp = fkeyid
from sysreferences
where rkeyid = @sysid
end
-- now drop the table
select @tablename = name from sysobjects where id = @sysid
set @sql = N'drop table ' + @tablename
exec sp_executesql @sql
set @sysid = null
select top 1 @sysid = id from sysobjects where name like 'r_%' and type = 'U'
end
P.S. This is SQL 2000. I think it would probably work in 7.0, but I don't know about 6.5.
