当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Buggy query for dropping databases

问题描述:

I've got a query that I've included into a batch file that should automatically drop databases before running through another process that will recreate and populate the databases again. I've used this query

declare @dbname nvarchar (2000);

declare @query nvarchar (max);

DECLARE db_cursor CURSOR FOR

select name from sys.databases

where name like '%ABCDir%'

Open db_cursor

fetch next from db_cursor into @dbname

while @@FETCH_STATUS = 0

BEGIN

set @query = 'Drop Database ['+ @dbname + ']'

Exec(@query)

FETCH NEXT FROM db_cursor INTO @dbname

END

Close db_cursor

deallocate db_cursor

The problem is its not deleting everything. The results from each run will populate another table and when I run the subsequent runs, I will get results for the previous runs in the current run. Anyboyd see errors in how this query is set up?

网友答案:

This script will make sure you are connected to that database, then change database mode to single user, it means only one user can be connected to a database, then you will connect to Master database it will leave database to be dropped with no connections, and finally you can drop the database.

Also use appropriate data types, your database will never be 2000 characters then why use a variable with varchar 2000, use SYSNAME data type specific for storing sql server object names.

Use QUOTENAME() function to put square brackets around your database name. makes your code look cleaner and protects you against a possible sql injection attack.

declare @dbname SYSNAME;
declare @query  NVARCHAR(max);

DECLARE db_cursor CURSOR FOR  
select name from sys.databases 
where name like '%ABCDir%' 

Open db_cursor
fetch next from db_cursor into @dbname

WHILE (@@FETCH_STATUS = 0) 
BEGIN
   SET @query = N' USE ' + QUOTENAME(@dbname) 
              + N' ALTER DATABASE ' + QUOTENAME(@dbname) 
              + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE; '  
              + N' USE master;'
              + N' Drop Database '  + QUOTENAME(@dbname) + '' 

  EXEC sp_executesql @query 

  FETCH NEXT FROM db_cursor INTO @dbname 

END

Close db_cursor
deallocate db_cursor
分享给朋友:
您可能感兴趣的文章:
随机阅读: