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

Question

I am doing a union of same table from different databases to get all the company details. Something like below.

CREATE PROCEDURE [dbo].[getCompanyDetails]
    (@Companycode varchar(2))
AS
BEGIN
    SELECT '[FirstDataBase]' DBNAME, CompanyID, CompanyName 
    FROM [FirstDataBase].dbo.tblCompany 
    WHERE CompanyID LIKE @Companycode+'%'

    UNION

    SELECT '[SecondDataBase]' DBNAME, CompanyID, CompanyName 
    FROM [SecondDataBase].dbo.tblCompany 
    WHERE CompanyID LIKE @Companycode+'%'

    UNION

    SELECT '[ThirdDataBase]' DBNAME, CompanyID, CompanyName 
    FROM [ThirdDataBase].dbo.tblCompany 
    WHERE CompanyID LIKE @Companycode+'%'

    UNION

    SELECT '[FourthDataBase]' DBNAME, CompanyID, CompanyName 
    FROM [FourthDataBase].dbo.tblCompany 
    WHERE CompanyID LIKE @Companycode+'%'
END

This is working fine and I am getting all company details, but I want to make sure that database is exist in the server.

Because if any one of the union database is not exist in the server then the stored procedure will fail. How to change the stored procedure so that even any of the union fails the other should work and give result?

PS: one extra question, is there any way I can union one database from another server?

seanb · Accepted Answer · 2020-10-11T08:42:22.043

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.

Sorry I missed that. I have edited my answer to show how to create your SQL statement using Dynamic SQL. — seanb, Oct 11 '20 at 08:28
... and have added temp table version, as well as notes about UNION vs UNION ALL — seanb, Oct 11 '20 at 08:43

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

1 Answers1