How to check for the existence of a DB?

Question

I am wondering if there is an elegant way to check for the existence of a DB? In brief, how do test the connection of a db connection string?

Thanks

Solution 1

Set the Initial Catalog=master in the connection string and execute:

select count(*) from sysdatabases where name = @name

with @name set to the name of the database.

If you want to check the connection string as a whole (and not existence of an independent database), try connecting to it in a try/catch block.

Solution 2

To cover the range of possibilities (server doesn't exist, database doesn't exist, no login, no permissions, server down, etc) - the simplest idea is simply to try to connect as normal, and perform something trivial - SELECT GETDATE() for example. If you get an exception, there is a problem!

There are times (especially when dealing with out-of-process systems) when try/catch is the most pragmatic option.

Solution 3

You could just try connecting to it. If it throws an exception, then the connection string is bad in some way, either the database doesn't exist, the password is wrong, or something else.

DbConnection db = new SqlConnection(connection_string);
try
{
    db.Open();
}
catch ( SqlException e )
{
    // Cannot connect to database
}

Solution 4

try

IF NOT EXISTS(SELECT * FROM sys.databases WHERE [name] = @name)
   CREATE DATABASE @name;
GO

or

IF db_id(@name) IS NOT NULL
   CREATE DATABASE @name;
GO

or SqlConnection.ChangeDatabase(String). I think it could use less sql server resources then new connection attempt.

Solution 5

If you're using Entity Framework or have it available to you, you can simply call Database.Exists():

if (Database.Exists(connectionString))
{
    // do something
}
else
{
    // do something else
}

View more solutions