Search code examples
sqlsql-serverdatabaseconditional-statementsdrop

SQL Dropping database with conditions

I'm working on some scripts for my Studies to create and Drop a Database.

For dropping the DB, I'd like to check if the Database exists or not - but I get an error message when using the IF condition together with "GO". This is what I intend to do:

If(db_id(N'Testdatabase') IS NOT NULL)
Begin
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'Testdatabase'
GO
use [Testdatabase];
GO
use [master];
GO
USE [master]
GO
ALTER DATABASE [Testdatabase] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [Testdatabase]
GO
END

I tried following the link here, which describes the usage of "SET NOEXEC ON;", But I'm not able to get this to work. TSQL - How to use GO inside of a BEGIN .. END block?

Does anybody have an idea how I could reach my goal? Any help would be appreciated!

Solution

  • You can use the code without GO:

    If(db_id(N'Testdatabase') IS NOT NULL)
Begin

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name ='Testdatabase'
USE [master]
ALTER DATABASE [Testdatabase] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
USE [master]
DROP DATABASE [Testdatabase]  
END