I have the following query that needs to be executed in different databases but table name and its structure is the same.
--query to be executed
select cust_num, name, credit_hold, credit_hold_reason,
when credit_hold_reason = 'NP' then 'No payments'
when credit_hold_reason = 'UK' then 'Unknown'
when credit_hold_reason = 'BK' then 'Bankruptcy'
end) as 'Type of credit hold reason',
--- from [database_02].[dbo].[custaddr]
--- from [database_03].[dbo].[custaddr]
order by credit_hold_reason asc
As you see, I just need to change the name of the database or "loop" in some sort of variable that holds the name of the databases, i.e.,
tables_in_loop = ['[database_01].[dbo].[custaddr]', 'from [database_02].[dbo].[custaddr]', 'from [database_03].[dbo].[custaddr]']
When you have the name of the databases, a connection is generated to them and all these databases are in the same server.
I do not have any approach or scratch code about this because this involves more depth in knowledge in SQL and unfortunately I am not an expert - yet. I read the following post SQL Server: how to get a database name as a parameter in a stored procedure and the approach to solve it seems similar here:
declare @dbname nvarchar(255);
set @dbname = 'db1';
if @dbname = 'db1'
else if @dbname = 'db2'
@dbname needs to increased to the next database and this is not specified in the link.
Any help or code in here would be appreciated.