Get database name dynamically-CodePudding

I am trying to get the database name dynamically but, showing some errors.

What I have tried:

Declare @dbname varchar(40) = (select name from sys.databases where name like '%worksdw2019')

select db_name() as [Database], *  
from @dbname.dbo.DimCustomer with (readuncommitted)

How end result should look like

select db_name() as [Database], * 
from AdventureWorksdw2019.dbo.dimcustomer

These are the errors I am getting:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.

Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

CodePudding user response：

You have invalid SQL written.

You cannot write use a variable like that in SQL Server. If the database name is going to be variable as seen in your example, you will need to execute dynamic SQL using the EXEC command such as this example below:

Declare @dbname varchar(40) = (select name from sys.databases where name like '%HelloWorld')

EXEC('select db_name() as [Database], *  from '   @dbname   '.dbo.DimCustomer with (readuncommitted);')

-- you may additionally need to escape your database name
-- if you are using special characters in your name

CodePudding user response：

You can't parameterize database names, so you'll need dynamic SQL.

DECLARE @context nvarchar(1000);

SELECT @context = QUOTENAME(name)   N'.sys.sp_executesql'
  from sys.databases 
  where name like N'%worksdw2019';
  -- what if there is more than one?

DECLARE @sql nvarchar(max) = N'select db_name() as [Database], *
  from dbo.DimCustomer;';

EXECUTE @context @sql;

Uh, with (readuncommitted) -> why? sqlblog.org/nolock