Baran Topal

Baran Topal


June 2018
M T W T F S S
« Feb    
 123
45678910
11121314151617
18192021222324
252627282930  

Categories


drop databases starting with the word ‘SS’ in mssql

baranbaran

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.