CODEWITHSUNDEEP
sql-serverdatabaset-sql
Ray
Ray

Reputation: 192186

How to check if a database exists in SQL Server?

What is the ideal way to check if a database exists on a SQL Server using TSQL? It seems multiple approaches to implement this.

Upvotes: 336

Views: 384940

Answers (7)

Wanjalize
Wanjalize

Reputation: 53

Try this if nothing else works. It's recommended by Microsoft.

USE tempdb;
GO
DECLARE @SQL nvarchar(1000);
IF EXISTS (SELECT 1 FROM sys.databases WHERE [name] = N'Sales')
BEGIN
    SET @SQL = N'USE [Sales];
    ALTER DATABASE Sales SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    USE [tempdb];

    DROP DATABASE Sales;';
    EXEC (@SQL);
END;

Change the sales part of the code with the name of the database that you want to delete.

Upvotes: 1

Eduardo
Eduardo

Reputation: 7841

Actually, it's best to use:

IF DB_ID('dms') IS NOT NULL
   --code mine :)
   print 'db exists'

See https://learn.microsoft.com/en-us/sql/t-sql/functions/db-id-transact-sql and note that this does not make sense with the Azure SQL Database.

Upvotes: 619

eKek0
eKek0

Reputation: 23289

From a Microsoft's script:

DECLARE @dbname nvarchar(128)
SET @dbname = N'Senna'

IF (EXISTS (SELECT name 
FROM master.dbo.databases 
WHERE ('[' + name + ']' = @dbname 
OR name = @dbname)))

Upvotes: 178

Reagan
Reagan

Reputation: 79

  Public Function SQLDatabaseExist(ByVal DefaultConnectionString As String, ByVal DataBaseName As String) As Boolean
Try
    'CREATE DATABASE
    Dim SqlString As String = ""
    SqlString = "SELECT CASE WHEN EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'" & DataBaseName & "') THEN CAST (1 AS BIT) ELSE CAST (0 AS BIT) END"
    Dim ExcRet As Integer = 0
    Using connection As New SqlConnection(DefaultConnectionString)
        Dim command As New SqlCommand(SqlString, connection)
        command.Connection.Open()
        ExcRet = command.ExecuteScalar()
        command.Connection.Close()
        command.Dispose()
    End Using
    Return ExcRet
Catch ex As Exception
    Return False
End Try

End Function

''Notice the initial catalog in the connection string must be master! 'Sample Default Connection String

Dim DefaultConnectionString As String = "Data Source=localhost\SQLSERVER2008;Initial Catalog=Master; User ID=SA; Password='123123'; MultipleActiveResultSets=false; Connect Timeout=15;Encrypt=False;Packet Size=4096;"

Upvotes: -1

Kovid Purohit
Kovid Purohit

Reputation: 288

TRY THIS

IF EXISTS 
   (
     SELECT name FROM master.dbo.sysdatabases 
    WHERE name = N'New_Database'
    )
BEGIN
    SELECT 'Database Name already Exist' AS Message
END
ELSE
BEGIN
    CREATE DATABASE [New_Database]
    SELECT 'New Database is Created'
END

Upvotes: 6

Don Rolling
Don Rolling

Reputation: 2339

I like @Eduardo's answer and I liked the accepted answer. I like to get back a boolean from something like this, so I wrote it up for you guys.

CREATE FUNCTION dbo.DatabaseExists(@dbname nvarchar(128))
RETURNS bit
AS
BEGIN
    declare @result bit = 0 
    SELECT @result = CAST(
        CASE WHEN db_id(@dbname) is not null THEN 1 
        ELSE 0 
        END 
    AS BIT)
    return @result
END
GO

Now you can use it like this:

select [dbo].[DatabaseExists]('master') --returns 1
select [dbo].[DatabaseExists]('slave') --returns 0

Upvotes: 10

si618
si618

Reputation: 16848

IF EXISTS (SELECT name FROM master.sys.databases WHERE name = N'YourDatabaseName')
  Do your thing...

By the way, this came directly from SQL Server Studio, so if you have access to this tool, I recommend you start playing with the various "Script xxxx AS" functions that are available. Will make your life easier! :)

Upvotes: 52

Related Questions