Script to delete all tables from the sql server database

From: dotnetolympians@yahoogroups.com On Behalf Of Mudassar Hassan
Excellent script to truncate all the tables from sql server database,
without deleting relationship b/w tables.
-- Start of Script - Truncate_ALL_ Tables.sql
/*
Find Table Reference Levels
This script finds table references and ranks them by level in order
to be able to load tables with FK references in the correct order.
Tables can then be loaded one level at a time from lower to higher.
This script also shows all the relationships for each table
by tables it references and by tables that reference it.
Level 0 is tables which have no FK relationships.
Level 1 is tables which reference no other tables, except
themselves, and are only referenced by higher level tables
or themselves.
Levels 2 and above are tables which reference lower levels
and may be referenced by higher levels or themselves.
*/
declare @r table (
PK_TABLE nvarchar(200) ,
FK_TABLE nvarchar(200) ,
primary key clustered (PK_TABLE,FK_ TABLE))
declare @rs table (
PK_TABLE nvarchar(200) ,
FK_TABLE nvarchar(200) ,
primary key clustered (PK_TABLE,FK_ TABLE))
declare @t table (
REF_LEVEL int,
TABLE_NAME nvarchar(200) not null primary key clustered )
declare @table table (
TABLE_NAME nvarchar(200) not null primary key clustered )
set nocount off
print 'Load tables for database '+db_name()
insert into @table
select
TABLE_NAME = a.TABLE_SCHEMA+ '.'+a.TABLE_ NAME
from
INFORMATION_ SCHEMA.TABLES a
where
a.TABLE_TYPE = 'BASE TABLE' and
a.TABLE_SCHEMA+ '.'+a.TABLE_ NAME <> 'dbo.dtproperties'
order by
1
print 'Load PK/FK references'
insert into @r
select distinct
PK_TABLE =
b.TABLE_SCHEMA+ '.'+b.TABLE_ NAME,
FK_TABLE =
c.TABLE_SCHEMA+ '.'+c.TABLE_ NAME
from
INFORMATION_ SCHEMA.REFERENTI AL_CONSTRAINTS a
join
INFORMATION_ SCHEMA.TABLE_ CONSTRAINTS b
on
a.CONSTRAINT_ SCHEMA = b.CONSTRAINT_ SCHEMA and
a.UNIQUE_CONSTRAINT _NAME = b.CONSTRAINT_ NAME
join
INFORMATION_ SCHEMA.TABLE_ CONSTRAINTS c
on
a.CONSTRAINT_ SCHEMA = c.CONSTRAINT_ SCHEMA and
a.CONSTRAINT_ NAME = c.CONSTRAINT_ NAME
order by
1,2
print 'Make copy of PK/FK references'
insert into @rs
select
*
from
@r
order by
1,2
print 'Load un-referenced tables as level 0'
insert into @t
select
REF_LEVEL = 0,
a.TABLE_NAME
from
@table a
where
a.TABLE_NAME not in
(
select PK_TABLE from @r union all
select FK_TABLE from @r
)
order by
1
-- select * from @r
print 'Remove self references'
delete from @r
where
PK_TABLE = FK_TABLE
declare @level int
set @level = 0
while @level < 100
begin
set @level = @level + 1
print 'Delete lower level references'
delete from @r
where
PK_TABLE in
( select TABLE_NAME from @t )
or
FK_TABLE in
( select TABLE_NAME from @t )
print 'Load level '+convert(varchar( 20),@level) +' tables'
insert into @t
select
REF_LEVEL =@level,
a.TABLE_NAME
from
@table a
where
a.TABLE_NAME not in
( select FK_TABLE from @r )
and
a.TABLE_NAME not in
( select TABLE_NAME from @t )
order by
1
if not exists (select * from @r )
begin
print 'Done loading table levels'
print ''
break
end
end
print 'Count of Tables by level'
print ''
select
REF_LEVEL,
TABLE_COUNT = count(*)
from
@t
group by
REF_LEVEL
order by
REF_LEVEL
print 'Tables in order by level and table name'
print 'Note: Null REF_LEVEL nay indicate possible circular reference'
print ''
select
b.REF_LEVEL,
TABLE_NAME = convert(varchar( 40),a.TABLE_ NAME)
from
@table a
left join
@t b
on a.TABLE_NAME = b.TABLE_NAME
order by
b.REF_LEVEL,
a.TABLE_NAME
print 'Tables and Referencing Tables'
print ''
select
b.REF_LEVEL,
TABLE_NAME = convert(varchar( 40),a.TABLE_ NAME),
REFERENCING_ TABLE =convert(varchar( 40),c.FK_ TABLE)
from
@table a
left join
@t b
on a.TABLE_NAME = b.TABLE_NAME
left join
@rs c
on a.TABLE_NAME = c.PK_TABLE
order by
a.TABLE_NAME,
c.FK_TABLE
print 'Tables and Tables Referenced'
print ''
select
b.REF_LEVEL,
TABLE_NAME = convert(varchar( 40),a.TABLE_ NAME),
TABLE_REFERENCED =convert(varchar( 40),c.PK_ TABLE)
from
@table a
left join
@t b
on a.TABLE_NAME = b.TABLE_NAME
left join
@rs c
on a.TABLE_NAME = c.FK_TABLE
order by
a.TABLE_NAME,
c.PK_TABLE
-- End of Script
Regards
Mudassar Hassan
http://mudassarhass an.spaces. live.com/