Tag Archives: DB_ID() function

How to check if a Database exists in Sql Server

Many a times we come across a scenario where we need to execute some code based on whether a Database exists or not. There are different ways of identifying the Database existence in Sql Server, in this article will list out the different approaches which are commonly used and it’s pros and cons. I prefer using the DB_ID() function as it is easy to remember. Let me know which approach you use and reason for the same.

To demo these different approaches let us create a sample database by the below script:

CREATE DATABASE SqlHintsDB
GO

[ALSO READ] How to check if a Table exists

Approach 1: Using DB_ID() function

We can use DB_ID() function like below to check if SqlHintsDB database exists. This is the simplest and easiest approach to remember

IF DB_ID('SqlHintsDB') IS NOT NULL
BEGIN
	PRINT 'Database Exists'
END

RESULT:
Database existence check using DB_ID() function

[ALSO READ] How to check if a Stored Procedure exists

Approach 2: Using sys.databases Catalog View

We can use the sys.databases catalog view to check the existence of the Database as shown below:

IF EXISTS(SELECT * FROM master.sys.databases 
          WHERE name='SqlHintsDB')
BEGIN
	PRINT 'Database Exists'
END

RESULT
Check Database existence using sys.databases catalog view

[ALSO READ] :How to check if a View exists

Approach 3: Avoid Using sys.sysdatabases System table

We should avoid using sys.sysdatabases System Table directly, direct access to it will be deprecated in some future versions of the Sql Server. As per Microsoft BOL link, Microsoft is suggesting to use the catalog view sys.databases instead of sys.sysdatabases system table directly.

IF EXISTS(SELECT * FROM master.sys.sysdatabases 
          WHERE name='SqlHintsDB')
BEGIN
	PRINT 'Database Exists'
END

RESULT:
Check Database existence using sys sysdatabases system table ver2

[ALSO READ] :
How to check if Temp table exists
How to check if a record exists in table
How to check if a Table exists
How to check if a Stored Procedure exists in Sql Server
How to check if a View exists