-- obliterate database
declare
@vcObj varchar(250),
@vcTable varchar(250),
@vcSchema varchar(250),
@temporalTable varchar(250)
-- ...drop foreign key constraints
select @vcObj = ''
while 1=1
begin
select @vcObj = min(name) from sysobjects where type='F' and name > @vcObj
if @vcObj is null break;
-- ...get name of table corresponding to the foreign key
select
@vcTable = S2.name,
@vcSchema = SS.name
from
sysobjects so
inner join sys.schemas SS on so.uid = ss.schema_id
inner join sysconstraints C on so.id = C.constid
inner join sysobjects S2 on C.id = S2.id
where so.type='F'
and so.name = @vcObj
print 'Dropping FK constraint ' + @vcObj
exec ('alter table ' + @vcSchema + '.[' + @vcTable + '] drop constraint [' + @vcObj + ']')
end
-- ...drop tables
select @vcObj = ''
while 1=1
begin
select
@vcObj = min(ss.name + '.[' + s.name + ']')
from
sysobjects s
inner join sys.schemas ss on s.uid = ss.schema_id
where
s.type='U'
and s.name not like 'dt%'
and (ss.name + '.' + s.name) > @vcObj
and ss.name in ('dbo', 'lookup')
if @vcObj is null break;
if (select temporal_type from sys.tables where object_id = object_id(@vcObj, 'u')) = 2
begin
set @temporalTable = replace(replace(@vcObj, 'dbo.', 'History.'), 'lookup.', 'History.')
print 'alter table ' + @vcObj + ' set (SYSTEM_VERSIONING = OFF)'
print 'drop table ' + @temporalTable
print 'drop table ' + @vcObj
exec ('alter table ' + @vcObj + ' set (SYSTEM_VERSIONING = OFF)')
exec ('drop table ' + @temporalTable)
exec ('drop table ' + @vcObj)
end
else
begin
print 'drop table ' + @vcObj
exec ('drop table ' + @vcObj)
end
end
-- ...drop history schema
if exists(select * from sys.schemas where name = 'History')
begin
drop schema History
end