1.刪除某一數(shù)據(jù)庫中所有外鍵約束
declare @sql varchar(max),
@tab_name varchar(128),
@fk_name varchar(128);
declare c cursor for
select OBJECT_NAME(parent_object_id), name from sys.objects where type='F'
open c
fetch next from c into @tab_name, @fk_name
while @@FETCH_STATUS=0
begin
set @sql='';
set @sql='alter table ' + @tab_name + ' drop constraint ' + @fk_name
print @sql
exec(@sql)
fetch next from c into @tab_name, @fk_name
end
close c
deallocate c
2. 刪除某一數(shù)據(jù)時提示“該數(shù)據(jù)庫正在使用中”
use master
go
declare @dbname sysname
set @dbname = 'BigValuesTest' --這個是要刪除的數(shù)據(jù)庫庫名
declare @s nvarchar(1000)
declare tb cursor local
for
select s = 'kill ' + cast(spid as varchar)
from master.dbo.sysprocesses
where dbid = DB_ID(@dbname)
open tb
fetch next from tb into @s
while @@fetch_status = 0
begin
exec (@s)
fetch next from tb into @s
end
close tb
deallocate tb
exec ('drop database [' + @dbname + ']')
參考文獻(xiàn)
無法刪除數(shù)據(jù)庫，因?yàn)樵摂?shù)據(jù)庫當(dāng)前正在使用"問題解決
SQLServer刪除所有外鍵
