How to check which database exists and use it in the function? [duplicate]

Question

Given different machines with sql server. All of them have the same databases. The only difference between these db's is their name. It could be two names, let's say 'DB1' and 'DB2'.

I need to check, which name is used on the given machine and create a function over it. The function is pretty big, it has at least 50 spots where the name of the DB is needed.

I was trying to do something like

DECLARE @dbName VARCHAR(20) SET @dbName = ISNULL(DB_ID('DB1'), 'DB2'); SELECT * FROM @dbName.dbo.testtable;

But it does not work. Any help would be appreciated.

Do you know the names of the databases in advance, or are they potentially variable? — Paul Williams
– Paul Williams, Commented Dec 9, 2013 at 17:25
@Paul Williams. I do. It will be either DB1 or DB2. No other possibilities. — Dzmitry Sevkovich
– Dzmitry Sevkovich, Commented Dec 9, 2013 at 17:32

D Stanley · Accepted Answer · 2013-12-09 17:28:48Z

3

No, that won't work. With only two possible database you may be better off with an if:

DECLARE @dbName VARCHAR(20) SET @dbName = CASE WHEN DB_ID('DB1') IS NULL THEN 'DB2' ELSE 'DB1' END; IF @dbName = 'DB1' SELECT * FROM DB1.dbo.testtable; ELSE SELECT * FROM DB2.dbo.testtable;

If you want to run ALL future queries in that scope against that database then dynamically run a USE statement instead:

IF @dbName = 'DB1' USE DB1; ELSE USE DB2;

edited Dec 9, 2013 at 17:28

answered Dec 9, 2013 at 17:16

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dzmitry Sevkovich Over a year ago

So basically, if I have 100 references to one of these db's, I have to use that if-else for each reference. Correct?

D Stanley Over a year ago

@DzmitrySevkovich if all of the queries are in the same scope than you can use a USE statement to change the database - see my edit.

Dzmitry Sevkovich Over a year ago

@D Stanley unfortunately, it gave me an error: a USE database statement is not allowed in a procedure, function or trigger.

D Stanley Over a year ago

Can you choose the database before running the procedure? The procedure should then run using that database.

Paul Williams · Accepted Answer · 2013-12-09 17:18:59Z

You can use dynamic SQL:

DECLARE @dbName VARCHAR(20) SET @dbName = 'DB2' IF DB_ID('DB1') IS NOT NULL SET @dbName = 'DB1' DECLARE @SQL NVARCHAR(100) SET @SQL = N'SELECT * FROM ' + @dbName + N'.dbo.testtable' EXEC sp_executesql @SQL

Rahul Tripathi · Accepted Answer · 2013-12-09 17:26:31Z

1

You may try like this using the DB_ID function:

IF DB_ID('DB1') IS NOT NULL PRINT 'exists'

Try like this:

DECLARE @db VARCHAR(20) SET @db = CASE WHEN DB_ID('DB1') IS NULL THEN 'DB2' ELSE 'DB1' END; IF @db = 'DB1' SELECT * FROM DB1.dbo.testtable; ELSE SELECT * FROM DB2.dbo.testtable;

edited Dec 9, 2013 at 17:26

answered Dec 9, 2013 at 17:15

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

7 Comments

Dzmitry Sevkovich Over a year ago

I actually used it. But I don't need to just check if it exists, but I want to use the name of existing db.

Rahul Tripathi Over a year ago

@DzmitrySevkovich:- I have updated my answer. Hope that helps!

Martin Smith Over a year ago

ISNULL(DB_ID('DB1'), 'DB2') will try and cast the string DB2 to an int if DB1 does not exist.

Rahul Tripathi Over a year ago

@MartinSmith:- Sir I didnt get that very clearly. Am I missing something. It would be great if you can edit my answer if that is wrong! :)

Paul Williams Over a year ago

@RahulTripathi: if you try to run that query, you get the error "Conversion failed when converting the varchar value 'DB2' to data type smallint."

|

M.Ali · Accepted Answer · 2013-12-09 17:30:39Z

DECLARE @dbName NVARCHAR(128); DECLARE @Sql NVARCHAR(MAX); SET @dbName = 'DataBaseName' IF db_id(@dbName) is not null BEGIN SET @Sql = N'SELECT * FROM ' + QUOTENAME(@dbName) + N'.[dbo].[TableName]' END ELSE BEGIN SET @Sql = N'SELECT * FROM ' + QUOTENAME(db2Name) + N'.[dbo].[TableName]' END EXECUTE sp_Executesql @Sql

Collectives™ on Stack Overflow

How to check which database exists and use it in the function? [duplicate]

4 Answers 4

4 Comments

Comments

7 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

7 Comments

Comments

Linked

Related