U vise navrata mi je bilo potrebno praznjenje/brisanje tabela, svih ili samo odredjenih, ali bez ubijanja constraintova.
Uglavnom sam to postizao rucnim odredjivanjem pravilnog redosleda praznjenja/brisanja tabela.
Jednom prilikom sam pokusao da napisem skriptu koja bi sam pronasla taj redosled. Nisam uspeo iz prve ali sam se kasnije vratio tome i uspeo.
Hteo sam da postujem na forumu kao mozgalicu pa sam se predomislio (mislio sam da nece nikome trebati).
Evo ko hoce moze da isproba.
Code:
declare @tables table(TABLE_NAME varchar(100), REFERENCES_TABLE varchar(100))
insert into @tables(TABLE_NAME, REFERENCES_TABLE)
select T1.TABLE_NAME, T4.TABLE_NAME as REFERENCES_TABLE
from INFORMATION_SCHEMA.TABLES T1
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS T2 on T1.TABLE_NAME = T2.TABLE_NAME and T2.CONSTRAINT_TYPE = 'FOREIGN KEY'
left join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS T3 on T3.CONSTRAINT_NAME = T2.CONSTRAINT_NAME
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS T4 on T3.UNIQUE_CONSTRAINT_NAME = T4.CONSTRAINT_NAME
where T1.TABLE_TYPE = 'BASE TABLE'
-----------------------------------------------------------------------------------------------------------------------
declare @drop table(TABLE_NAME varchar(100))
while (select count(*) from @tables) > 0
begin
insert into @drop(TABLE_NAME)
select distinct TABLE_NAME from @tables
where TABLE_NAME not in (select distinct REFERENCES_TABLE from @tables where REFERENCES_TABLE is not null)
delete from @tables
where TABLE_NAME in (select TABLE_NAME from @drop)
end
-----------------------------------------------------------------------------------------------------------------------
declare @table_name varchar(100), @query varchar(120)
declare tables cursor for select TABLE_NAME from @drop
open tables
fetch next from tables into @table_name
while @@FETCH_STATUS = 0 and @@ERROR = 0
begin
set @query = 'drop table [' + @table_name + ']'
print @query
exec(@query)
if @@ERROR <> 0 BREAK
fetch next from tables into @table_name
end
close tables
deallocate tables