325

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

Ray
  • 187,153
  • 97
  • 222
  • 204

6 Answers6

600

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.

Eduardo
  • 7,631
  • 2
  • 30
  • 31
  • 5
    Well it's certainly shorter and more cryptic. Out of curiosity, why is it better? – Mike K Jun 25 '14 at 20:05
  • 8
    Presumably because db_id is safer than checking for a database name in a specific location in `[master]` – Anthony Aug 23 '14 at 03:45
  • 4
    Well, yeah, plus that it is nearly impossible for db_id() to be worse (could be the same complexity/cost) than the accepted answer since db_id queries for a number. So I rather bet on db_id() being implemented in a smarter way, since it was done by the database developers. – Eduardo Aug 24 '14 at 06:08
  • 3
    If you have permission issues, like you don't have permission to access [master] this works well! – Jason Foglia Apr 26 '16 at 20:45
  • 2
    Not sure this works in all cases... I tried it and it gave incorrect results so I wrote a quick test: use [X] go if db_id('[X]') IS NULL print 'db does not exist' When I ran this I found that the db_id call seems to always return null. – Roger Hill Apr 29 '16 at 21:13
  • Don't see how it could not work, given official documentation: https://msdn.microsoft.com/en-us/library/ms186274.aspx – Eduardo Apr 29 '16 at 21:29
  • 2
    How to check the same for a linked server? – Anton Shepelev May 27 '16 at 13:08
  • It has already been answered here http://stackoverflow.com/questions/3104186/sql-server-is-there-an-if-exists-test-for-a-linked-server – Eduardo May 27 '16 at 14:26
  • 3
    @MadTigger: you shouldn't include `[ ]` in your call to `db_id`; that's SQL syntax, not part of the database name. – Jacob Krall Jul 07 '17 at 15:18
  • How to do this for another server? ie: check if `[server].[db]` exists? The answer linked by @Eduardo still uses "if exists" syntax. – undrline - Reinstate Monica Feb 14 '21 at 18:47
  • This does not work for SQL Server, Incorrect syntax near 'NULL'. – RobyB Jul 20 '21 at 07:43
  • @RobyB this is for Microsoft SQL Server, and works. Let us know what you are running. – Eduardo Jul 20 '21 at 13:22
  • @Eduardo This Syntax doesn't work for: Microsoft SQL Server Developer (64-bit), Platform: Linux , Version: 15.0.4073.23. – RobyB Jul 20 '21 at 14:19
179

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)))
Community
  • 1
  • 1
eKek0
  • 23,005
  • 25
  • 91
  • 119
  • 7
    That may be from a Microsoft script but it's not Microsoft recommended practice. They encourage using the INFORMATION_SCHEMA views rather than directly accessing the system tables. – mwigdahl Mar 24 '09 at 20:09
  • shouldn't "NOT EXISTS" just be "EXISTS" – KM. Mar 24 '09 at 20:14
  • 4
    why is encourage using INFORMATION_SCHEMA instead of directly using references to tables? – eKek0 Mar 24 '09 at 20:17
  • 5
    In general it's because Microsoft commits to the format INFORMATION_SCHEMA, and reserves the right to change the system tables as they please. But in this case, after looking more closely, INFORMATION_SCHEMA doesn't work, so this is probably the best option. – mwigdahl Mar 24 '09 at 20:21
  • right from my SQL Server 2005 online docs: REFERENTIAL_CONSTRAINTS (Transact-SQL) This feature has changed from earlier versions of SQL Server. For more information, see Behavior Changes to Database Engine Features in SQL Server 2005. – KM. Mar 24 '09 at 20:33
  • I would rather use the less verbose and more abstracted sys.(dot)databases, which is a system view, not table. See below. – ProfK Mar 25 '09 at 15:49
  • 3
    I agree INFORMATION_SCHEMA is preferred for checking objects ~inside a database. But can INFORMATION_SCHEMA to used to check for the db itself? <<<<< ............... CHECK_CONSTRAINTS Check Constraints COLUMN_DOMAIN_USAGE Every column that has a user-defined data type. COLUMN_PRIVILEGES Every column with a privilege granted to or by the current user in the current database. COLUMNS Lists every column in the system CONSTRAINT_COLUMN_USAGE Every column that has a constraint defined on it. CONSTRAINT_TABLE_USAGE Every table that has a constraint defined on it. – granadaCoder Aug 05 '11 at 13:30
  • DOMAIN_CONSTRAINTS Every user-defined data type with a rule bound to it. DOMAINS Every user-defined data type. KEY_COLUMN_USAGE Every column that is constrained as a key PARAMETERS Every parameter for every user-defined function or stored procedure in the database. For functions this returns one row with return value information. REFERENTIAL_CONSTRAINTS Every foreign constraint in the system.a ROUTINE_COLUMNS Every column returned by table-valued functions. ROUTINES Every stored procedure and function in the database. SCHEMATA Every database in the system. – granadaCoder Aug 05 '11 at 13:32
  • TABLE_CONSTRAINTS Every table constraint. TABLE_PRIVILEGES Every table privilege granted to or by the current user. TABLES Every table in the system. ........... From http://haacked.com/archive/2006/07/05/bulletproofsqlchangescriptsusinginformation_schemaviews.aspx (but wanted to list the information here in case the URL possibly dies sometime in the future). VIEW_COLUMN_USAGE Every column used in a view definition. VIEW_TABLE_USAGE Every table used in a view definition. VIEWS Every View – granadaCoder Aug 05 '11 at 13:32
  • 4
    @mwigdahl - Please provide a reference for this claimed recommended practice. – Martin Smith Dec 05 '11 at 16:02
  • 2
    Here are a couple of links: http://msdn.microsoft.com/en-us/library/ms186778.aspx ("Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.") Also, the more strongly-worded older version: http://msdn.microsoft.com/en-us/library/aa933204%28v=sql.80%29.aspx – mwigdahl Dec 05 '11 at 20:02
  • 1
    The answer seems to be way. INFORMATION_SCHEMA is only for within a database (so if you already know it exists). Unless @mwigdahl can provide one. – Jeroen K Oct 01 '13 at 12:15
49
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! :)

si618
  • 16,580
  • 12
  • 67
  • 84
  • 4
    If 'USE [Master]' is inconvenient, you can directly address the view view from any database as 'master.sys.databases' – ProfK Mar 25 '09 at 15:51
10

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
Don Rolling
  • 2,301
  • 4
  • 30
  • 27
5

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
Kovid Purohit
  • 258
  • 1
  • 4
  • 15
0
  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;"
Reagan
  • 71
  • 5