使用场景:
A(EAP_Accout)表中存在其他数据库名的信息,需要根据A表查出相应库名信息后,再将A所在的库进行删除
解决方式:
使用游标依次表中对应的库名信息,进行删库操作
Use UFTSystem
GO
Declare @Name varchar(20)
declare @sql nvarchar(300)
DECLARE acc_cursor CURSOR FOR
select DSName from EAP_Account order by cacc_Num
OPEN acc_cursor
--
FETCH NEXT FROM acc_cursor into @Name
WHILE @@FETCH_STATUS =0
BEGIN
print @Name
IF EXISTS(SELECT * FROM master.dbo.sysdatabases where name=@Name)
BEGIN
set @sql =N'ALTER DATABASE '+ @Name +' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
drop database '+@Name+'; '
exec sp_executesql @sqlEND
FETCH NEXT FROM acc_cursor into @Name
END
CLOSE acc_cursor
DEALLOCATE acc_cursor
delete EAP_Account
--GO
--use master
--ALTER DATABASE UFTSystem SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
--drop database UFTSystem;
遇到问题:
删库时不成功--原因存在其他连接,无法执行删除操作
解决方法:设置成单连接 SET SINGLE_USER WITH ROLLBACK IMMEDIATE