1

I've tried looking at other answers to this, but none seem to match/work for me.

I have the below SQL query running perfectly in SSMS. However, I want to add a column to show the database name from where it is pulling the data from as I am running the query in multiple databases with %AccountsLive.

Is there a way to be able to do this? Thanks in advance!

DECLARE @Sql NVARCHAR(MAX);
SET @Sql =
STUFF(
(SELECT
    NCHAR(10) + N'UNION ALL' + NCHAR(10) +
N'SELECT
    ST_ALOC_POINTER COLLATE DATABASE_DEFAULT AS ''Cust_Code'',
    ST_TRANTYPE COLLATE DATABASE_DEFAULT AS ''Transaction'',
    ST_HEADER_REF COLLATE DATABASE_DEFAULT AS ''Reference'',
    ST_GROSS AS ''Local_Gross'',
    ST_CURRENCYCODE COLLATE DATABASE_DEFAULT AS ''Currency'',
    ST_CURR_VALU AS ''Foreign_Gross'',
    ST_DESCRIPTION COLLATE DATABASE_DEFAULT AS ''Description''
FROM ' + QUOTENAME(d.name) + N'.dbo.SL_TRANSACTIONS
WHERE ST_YEAR = ''C''
and ST_PERIODNUMBER = ''1''
and ST_ALOC_POINTER like ''%LC%''
and ST_GROSS <> ''0''
UNION ALL
SELECT
    ST_ALOC_POINTER COLLATE DATABASE_DEFAULT AS ''Cust_Code'',
    ST_TRANTYPE COLLATE DATABASE_DEFAULT AS ''Transaction'',
    ST_HEADER_REF COLLATE DATABASE_DEFAULT AS ''Reference'',
    ST_GROSS AS ''Local_Gross'',
    ST_CURRENCYCODE COLLATE DATABASE_DEFAULT AS ''Currency'',
    ST_CURR_VALU AS ''Foreign_Gross'',
    ST_DESCRIPTION COLLATE DATABASE_DEFAULT AS ''Description''
FROM ' + QUOTENAME(d.name) + N'.dbo.SL_TRANSACTIONS
WHERE ST_YEAR = ''C''
and ST_PERIODNUMBER = ''1''
and ST_ALOC_POINTER like ''%CR%''
and ST_GROSS <> ''0'''

FROM sys.databases d
where name like '%AccountsLive'
FOR XML PATH(''), TYPE)

.value('text()[1]','nvarchar(max)'), 1, 11, '');

exec( @Sql );
Andreas
  • 154,647
  • 11
  • 152
  • 247
PeterK
  • 99
  • 6
  • 2
    Did you try a web search for something like [`sql server get database name`](https://www.google.com/search?q=sql+server+get+database+name) before you asked here? You should have! Think how much faster you'd have gotten an answer if you had. – Andreas Mar 15 '21 at 01:49
  • Just add as a column in the dynamic part `N''' + QUOTENAME(d.name, '''') + N''' AS dbName` @stickybit Linked duplicate is wrong, that shows the `USE DB;` of the currently executing query, not which table is being read. – Charlieface Mar 15 '21 at 01:59
  • @Andreas all I kept on getting was `select db_name()` when searching originally, which wouldn't work in my particular query....normal query, yes definitely would – PeterK Mar 15 '21 at 02:18
  • @Charlieface thank you very much for your help with trying to solve this....however, am getting an `incorrect syntax near ''` with this....I've tried `N' + QUOTENAME(d.name, '') + N' AS dbName` but getting the same. After doing a `print` and looking and doing it as `' + QUOTENAME(d.name, '') + N' AS dbName` I get an `incorrect syntax near 'ST_ALOC_POINTER'`. Do you know where this could be going wrong? – PeterK Mar 15 '21 at 02:21
  • @Charlieface I was being an idiot and not adding the comma at the end!! However, I get an `incorrect column name` after I tweaked yours slightly to `' + QUOTENAME(d.name, '') + N' AS dbName,`? – PeterK Mar 15 '21 at 02:32
  • 1
    Sorry yeah, take off the outer apostrophes as you did, but leave in the quad `''''` in the parameter so `' + QUOTENAME(d.name, '''') + N' AS dbName,` – Charlieface Mar 15 '21 at 02:47
  • Brilliant! Thank you very much for your help with this! – PeterK Mar 15 '21 at 02:49

0 Answers0