SQL批量刪除表時(shí)，如果表有外鍵，就不能直接刪除，下面將為您介紹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#p#
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