日韩无码专区无码一级三级片|91人人爱网站中日韩无码电影|厨房大战丰满熟妇|AV高清无码在线免费观看|另类AV日韩少妇熟女|中文日本大黄一级黄色片|色情在线视频免费|亚洲成人特黄a片|黄片wwwav色图欧美|欧亚乱色一区二区三区

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

分享標(biāo)題：SQL批量刪除含有外鍵的表的方法
網(wǎng)址分享：http://www.5511xx.com/article/dphjgdc.html