drop all tables in database by T-SQL in SQLServer

最新推荐文章于 2025-06-07 19:24:53 发布

原创最新推荐文章于 2025-06-07 19:24:53 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#database #sqlserver #sql server #table #security #delete

本文介绍了一种使用 T-SQL 脚本快速清空 SQL Server 数据库中所有表的方法，适用于 Windows 2003 + SQL Server 2005 环境。该方法通过编写自定义函数实现，能够批量删除数据库内的所有表，同时忽略外键约束错误。

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

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

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