0

I have the following piece of script that loops through multiple identical databases (55 of them). I am trying to get the database name as a column, but it keeps giving me the master db_name as this is where I execute from. When I change it to something else, then I get that db_name, but never the correct one based on the database in question.

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + N'
UNION ALL 
SELECT top 1
    Account as Account,
    DB_NAME() as DatabaseName,
    (Select CompnyName from ' + QUOTENAME(name) + '.dbo.OADM)as Entity,
    GL.TransId as TransactionId,    
        isnull(Debit,0) as Debit,
        isnull(Credit,0) as Credit,
        isnull(Debit,0)- isnull(Credit,0) as Balance,
        year(GL.refdate)*10000+month(GL.refdate)*100+day(GL.refdate) as TimePeriod
FROM ' + QUOTENAME(name) + '.dbo.JDT1 GL'
FROM master.sys.databases WHERE 
state = 0 
and database_id > 8 
and name not like '%template%'
and name not like '%staging%'
and name not like '%test%'

SET @sql = STUFF(@sql, 1, 11, '');

EXEC  (@sql);
--Print @sql

The rest works great, it is just the db_name that I need added.

Please help. Thanks, Wynand

I tried using DB_ID() with and without quotes, but it keeps executing against the master database.

Wynand Cilliers
  • 1
  • 1
  • The `QUOTENAME(name)` section is already inserting the database name into the query several times, it could easily do it another time, with suitable quoting. However, please [be aware](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-local-variable-transact-sql?view=sql-server-ver16#remarks): "Don't use a variable in a SELECT statement to concatenate values (that is, to compute aggregate values). Unexpected query results may occur because all expressions in the SELECT list (including assignments) aren't necessarily run exactly once for each output row" – Damien_The_Unbeliever Feb 06 '23 at 07:10
  • @Damien_The_Unbeliever Thank you for your reply. I understand what you are saying but I am unable to achieve the result. It doesn't seem to work. Even if I try to use quotename(name) I still cannot get it to work. I think you are right, it is to do with the quoting, but I am struggling to get it right. – Wynand Cilliers Feb 06 '23 at 07:18

0 Answers0