Long time ago, I wanted to drop databases starting with the word ‘SS’ in mssql. I didn’t want to do this manually so I wrote the following script.
use master
DECLARE @Name nvarchar(1000);
DECLARE testdb_cursor CURSOR FOR SELECT 'ALTER DATABASE' + '[' + NAME + ']' +
' SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE ' + '[' + NAME + ']' FROM sys.sysdatabases
where name like 'RS%'
OPEN testdb_cursor; -- Perform the first fetch and store the value in a variable.
FETCH NEXT FROM testdb_cursor INTO @Name; -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0 BEGIN -- Concatenate and display the current values in the variables.
exec sp_executesql @Name; -- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM testdb_cursor INTO @Name;
END
CLOSE testdb_cursor;
DEALLOCATE testdb_cursor;
Following is the mssql management studio view for MAC-ASDF named instance with 3 databases, RS1, RSS and SS.
After executing the script, only SS db exists.