If your datasource’s credential has appropriate rights, you can do all that from Lucee.
Do a cfquery like this
SELECT * FROM master.sys.databases WHERE is_read_only=0 and is_in_standby=0
You can loop over the results and pull tables from all, i.e.
SELECT <cfqueryparam value="#dbName#"/> as DBName, * FROM [#dbName#].sys.tables
You could further concatenate each of those queries together.
Or you can use the datasource and override parts of it, like the username and password…
One of the mgmt jobs I have here loops over all databases (300+) on my production cluster and gets usage stats and aggregates them in another table. I have a datasource with no username or password defined for each server - i.e. PRODSQLV01, PRODSQLV02, with the server hostname, port, and master as the database, blank username and password. (Because the tool asks for the credentials of a db user who has sufficient rights to do this)
Then I read my database of DB Names, Source Servers, Usernames and passwords (encrypted), loop over the results and do something like:
<cfquery datasource="#sourceserver#" username="#usercred#" password="#password#">
SELECT <stuff> FROM [#dbname#].dbo.Table1 A inner join [#dbname#].dbo.Table2 ......
Get my numbers and insert into my Agg database.
Note when using 3 dot syntax you CANNOT put dbname in a queryparam - then you’d be telling SQL that it’s a variable, NOT an object name. Yes, this does open you up to SQL injection so be sure your db name variable actually contains a database name, and do whatever you can to make sure it can’t be abused.