Skip to content
Advertisement

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

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

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?

Advertisement

Answer

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.,

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.

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.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement