Check If Database Exists Before Creating

How to check if a database exists in SQL Server?

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)))

Check If Database Exists in SQL Server

Wrap the expression using the potentially non existent database in EXEC so it is only compiled if that branch is taken.

I used some shorter idioms for checking database and column existence. If you stick with your existing one for columns it will need to go inside the string that is EXECed with the quotes doubled up to escape them.

DECLARE @dbname sysname
SET @dbname = N'Courses'

IF DB_ID(@dbname) IS NOT NULL
  BEGIN
    IF COL_LENGTH('Courses.dbo.Course','isAvailableOnline') IS NULL
    BEGIN
       EXEC('ALTER TABLE Courses.dbo.Course ADD isAvailableOnline BIT NULL')
    END
  END

How to check if mysql database exists

SELECT SCHEMA_NAME
  FROM INFORMATION_SCHEMA.SCHEMATA
 WHERE SCHEMA_NAME = 'DBName'

If you just need to know if a db exists so you won't get an error when you try to create it, simply use (From here):

CREATE DATABASE IF NOT EXISTS DBName;

How to check if a database exists before creating a table?

You can check if a database exists with databaseExists(String path).

https://github.com/tekartik/sqflite/blob/master/sqflite/lib/sqflite.dart (line 174)

/// Check if a database exists at a given path.
///
Future<bool> databaseExists(String path) =>
    databaseFactory.databaseExists(path);

But I think you are concerned with the CREATE TABLE statement being called again. You should not worry about this if you specify the version. Internally the version is kept and onCreate isn't called if it's already specified.

From the same file:

/// If [version] is specified, [onCreate], [onUpgrade], and [onDowngrade] can
/// be called. These functions are mutually exclusive — only one of them can be
/// called depending on the context, although they can all be specified to
/// cover multiple scenarios
///
/// [onCreate] is called if the database did not exist prior to calling
/// [openDatabase]. You can use the opportunity to create the required tables
/// in the database according to your schema

What's the simplest way to check if database exists in MSSQL using VB.NET?

Connect to a system-db (master, msdb, tempdb or model) - because you can be sure that they exist! Then you can select the list of database like this:

select * from sys.databases 

or if you want to know if a specific db exists:

select * from sys.databases where name = 'NameOfYourDb'

If you connect without a database name in your connection string (belongs to which provider you are using) you should automatically be connect to your default database (which is "master" by default)

Check if Database Exists Before Creating

As of SQL Server 2005, the old-style sysobjects and sysdatabases and those catalog views have been deprecated. Do this instead - use the sys. schema - views like sys.databases

private static bool CheckDatabaseExists(SqlConnection tmpConn, string databaseName)
{
    string sqlCreateDBQuery;
    bool result = false;

    try
    {
        tmpConn = new SqlConnection("server=(local)\\SQLEXPRESS;Trusted_Connection=yes");

        sqlCreateDBQuery = string.Format("SELECT database_id FROM sys.databases WHERE Name 
        = '{0}'", databaseName);

        using (tmpConn)
        {
            using (SqlCommand sqlCmd = new SqlCommand(sqlCreateDBQuery, tmpConn))
            {
                tmpConn.Open();

                object resultObj = sqlCmd.ExecuteScalar();

                int databaseID = 0;    

                if (resultObj != null)
                {
                    int.TryParse(resultObj.ToString(), out databaseID);
                }

                tmpConn.Close();

                result = (databaseID > 0);
            }
        }
    } 
    catch (Exception ex)
    { 
        result = false;
    }

    return result;
}

This will work with any database name you pass in as a parameter, and it will return a bool true = database exists, false = database does not exist (or error happened).

Check if Database exists MSSQL C#

1. Use parameterized queries.
2. Use Select count(*) instead of Select *.
3. Use ExecuteScalar instead of ExecuteNonQuery
4. Note the remarks on the code, they explain the changes I've made.

// No point of passing a bool if all you do is return it...
private bool CheckDatabase(string databaseName)
{
    // You know it's a string, use var
    var connString = "Server=localhost\\SQLEXPRESS;Integrated Security=SSPI;database=master";
    // Note: It's better to take the connection string from the config file.

    var cmdText = "select count(*) from master.dbo.sysdatabases where name=@database";

    using (var sqlConnection = new SqlConnection(connString))
    {
        using (var sqlCmd = new SqlCommand(cmdText, sqlConnection))
        {
            // Use parameters to protect against Sql Injection
            sqlCmd.Parameters.Add("@database", System.Data.SqlDbType.NVarChar).Value = databaseName;

            // Open the connection as late as possible
            sqlConnection.Open();
            // count(*) will always return an int, so it's safe to use Convert.ToInt32
            return Convert.ToInt32( sqlCmd.ExecuteScalar()) == 1;
        }
    }

}

How to check if a given database exists on the server?

Simplest is IF DB_ID('yourdb') IS NOT NULL

For discussion etc see How to check if a database exists in SQL Server?

One way to use this with the select statements is to use dynamic SQL to construct an SQL statement then run it e.g.,

DECLARE @CustomSQLSelect nvarchar(4000)

SET @CustomSQLSelect = 
    CASE WHEN DB_ID('FirstDataBase') IS NOT NULL
         THEN ' UNION Select ''[FirstDataBase]'' DBNAME,CompanyID ,CompanyName 
              From [FirstDataBase].dbo.tblCompany Where CompanyID like ''' + @Companycode + ''' +''%'''
         ELSE '' END
    + CASE WHEN DB_ID('SecondDataBase') IS NOT NULL
         THEN ' UNION Select ''[SecondDataBase]'' DBNAME,CompanyID ,CompanyName 
              From [SecondDataBase].dbo.tblCompany Where CompanyID like ''' + @Companycode + ''' +''%'''
         ELSE '' END
    + CASE WHEN DB_ID('ThirdDataBase') IS NOT NULL
         THEN ' UNION Select ''[ThirdDataBase]'' DBNAME,CompanyID ,CompanyName 
              From [ThirdDataBase].dbo.tblCompany Where CompanyID like ''' + @Companycode + ''' +''%'''
         ELSE '' END
    + CASE WHEN DB_ID('FourthDataBase') IS NOT NULL
         THEN ' UNION Select ''[FourthDataBase]'' DBNAME,CompanyID ,CompanyName 
              From [FourthDataBase].dbo.tblCompany Where CompanyID like ''' + @Companycode + ''' +''%'''
         ELSE '' END;

-- You may want to put in a PRINT @CustomSQLSelect to review the SQL

IF @CustomSQLSelect LIKE ' UNION %' 
    BEGIN
    
    SET @CustomSQLSelect = STUFF(@CustomSQLSelect, 1, 7, ''); -- Delete the first ' UNION ' statement

    EXEC (@CustomSQLSelect);

    END
ELSE
    BEGIN
    PRINT 'No valid databases!'
    END;

I think I got the apostrophes etc correct in the above SQL.

You could also just create a temporary table and fill it ... this is easier to maintain in my opinion.

CREATE TABLE #Companies (
    DBName nvarchar(100), 
    CompanyId varchar(20), 
    CompanyName nvarchar(100)
    )
-- Note - set your nvarchar/varchars appropriately

IF DB_ID('FirstDatabase') IS NOT NULL
    BEGIN
    INSERT INTO #Companies (DBName, CompanyID, CompanyName)
      Select '[FirstDataBase]' DBNAME,CompanyID ,CompanyName 
      From   [FirstDataBase].dbo.tblCompany 
      Where CompanyID like @Companycode+'%'
    END

-- Then do the same for other databases

Note that viewing your queries in SSMS may indicate errors if the databases don't exist.

Also note that a UNION (rather than a UNION ALL) will effectively change the 'SELECT' statements into 'SELECT DISTINCT' to stop duplicates. This adds extra processing.

The temporary table version doesn't have that. It's up to you to add the 'DISTINCT' if desired.

Alternatively, if you're happy with duplicates, or you know there are no duplicates (e.g., companyID is a PK or otherwise unique within each database) then changing the UNIONs to UNION ALL may speed up processing.

For reading data from other servers - one way is to use Linked servers. These are set up at server level I believe. You'll need to do research to see if this is right for you.

VB.net Checking if database exists before connecting to it

Change Print() to Print (remove the parentheses.)

---

Better, don't use Print at all, use select.

Dim sql As String = "if db_id('thedbName') is not null " & vbCrLf & _
                        "select 'exists' " & vbCrLf & _
                    "else " & vbCrLf & _
                        "select 'nope'"

Dim blah As String = CType(cmd.ExecuteScalar(), string)

ExecuteNonQuery returns the number of affected rows for updates and inserts. But what you are executing is a query.

ExecuteScalar returns the first column of the first row selected. The query above only returns one row with one value, so that's what it will return.

---

Related Topics