firstly I searched this topic in Google, but unfortunately, I cannot find any valueable article to
resolve it. someone think give an " drop database ; create database" idea.but always it doesn't match for our true intention. the reason is that this solution will delete all the related objects and even serious, the security (previleges grant) are also deleted. It make the table restore difficult.
in fact,it is really easy to implement it by an T-SQL.
following is my solution. (enviorment windows2003+SQL SERVER2005).
--it's my first time to write T-SQL function,share it for commemoration:-)
--delete all tables under current DB
--written by shenjian @ 2006/03/17
--if "foreign key constraint error" occur,just ignore it.
declare
@tblName varchar(2000),
@count int ,
@ExecStr varchar(2000),
@localcounter int
--written by shenjian @ 2006/03/17
--if "foreign key constraint error" occur,just ignore it.
declare
@tblName varchar(2000),
@count int ,
@ExecStr varchar(2000),
@localcounter int
set @count=0
set @localcounter=1 --only need a <>0 value to start the loop,no care what it is
while @localcounter<>0 --to deal the foreign key constraint
begin
set @localcounter=0
--cursor should be cycled for next time
declare curName cursor for
select name from sys.tables order by name
open curName
set @localcounter=1 --only need a <>0 value to start the loop,no care what it is
while @localcounter<>0 --to deal the foreign key constraint
begin
set @localcounter=0
--cursor should be cycled for next time
declare curName cursor for
select name from sys.tables order by name
open curName
fetch curName into @tblName
while @@fetch_status =0
begin
set @localcounter=@localcounter+1
select @ExecStr='drop table " '+@tblName+'"'
--print('SQL string:'+@ExecStr)
EXEC (@ExecStr)
if(@@error<>0)
begin
print(@@error)
end
fetch curName into @tblName
end
set @count=@count+@localcounter
close curName
deallocate curName
end
print('.....drop all table success....')
print('drop '+convert(varchar(5),@count)+' tables')
go
while @@fetch_status =0
begin
set @localcounter=@localcounter+1
select @ExecStr='drop table " '+@tblName+'"'
--print('SQL string:'+@ExecStr)
EXEC (@ExecStr)
if(@@error<>0)
begin
print(@@error)
end
fetch curName into @tblName
end
set @count=@count+@localcounter
close curName
deallocate curName
end
print('.....drop all table success....')
print('drop '+convert(varchar(5),@count)+' tables')
go