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%'
fetch next from db_cursor into @dbname
while @@FETCH_STATUS = 0
set @query = 'Drop Database ['+ @dbname + ']'
FETCH NEXT FROM db_cursor INTO @dbname
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