sql 批量删除数据库中的表 （含有外键约束）

 写法一：
set xact_abort on
begin tran
DECLARE @SQL VARCHAR(99)
DECLARE CUR_FK CURSOR LOCAL FOR
SELECT 'alter table '+ OBJECT_NAME(FKEYID) + ' drop constraint ' +    OBJECT_NAME(CONSTID) FROM SYSREFERENCES
--删除所有外键
OPEN CUR_FK
FETCH CUR_FK INTO @SQL
WHILE @@FETCH_STATUS =0
    BEGIN
        EXEC(@SQL)
        FETCH CUR_FK INTO @SQL
    END
CLOSE CUR_FK
DEALLOCATE CUR_FK
-- 删除所有表    
DECLARE CUR_TAB CURSOR LOCAL FOR
SELECT 'DROP TABLE '+ NAME FROM SYSOBJECTS WHERE XTYPE='U' -- AND NAME LIKE 'xx%'
OPEN CUR_TAB
FETCH CUR_TAB INTO @SQL
WHILE @@FETCH_STATUS =0
    BEGIN
        EXEC(@SQL)
        FETCH CUR_TAB INTO @SQL
    END
CLOSE CUR_TAB 
DEALLOCATE CUR_TAB
commit tran

写法二
DECLARE @SQL VARCHAR(99),@TBL VARCHAR(30),@FK VARCHAR(30)
DECLARE CUR_FK CURSOR LOCAL FOR
SELECT OBJECT_NAME(CONSTID),OBJECT_NAME(FKEYID) FROM SYSREFERENCES
--删除所有外键
OPEN CUR_FK
FETCH CUR_FK INTO @FK,@TBL
WHILE @@FETCH_STATUS =0
BEGIN
SELECT @SQL='ALTER TABLE '+@TBL+' DROP CONSTRAINT '+@FK
EXEC(@SQL)
FETCH CUR_FK INTO @FK,@TBL
END
CLOSE CUR_FK
DECLARE CUR_FKS CURSOR LOCAL FOR
SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U'
OPEN CUR_FKS
FETCH CUR_FKS INTO @TBL
WHILE @@FETCH_STATUS =0
BEGIN
SELECT @SQL='DROP TABLE ['+@TBL+']'
EXEC(@SQL)
FETCH CUR_FKS INTO @TBL
END
CLOSE CUR_FKS 

如果没有外键约束的话，也可以直接执行下面的SQL语句

declare @sql varchar(8000)
select @sql=isnull(@sql+' drop table ','')+'['+name+']'
from (select name from sysobjects where xtype='u')t
exec('drop table '+ @sql)