Q: How to loop on multiple databases and tables I have a SQL Server instance which contains multiple databases. I have 2 tables which exist in all databases on the server:

*

*Refresh Log

*Detailed refresh log.

I want to union all the tables across all databases on the server so the final result will be 2 tables which are the union refresh log and detailed refresh log.
I need help to write the function which runs across all databases.

A: I'm also a little uncertain as to what you're hoping for, for example if you need the resulting output to be in two permanent tables or if you just need the result when queried.  Of course once you build your SELECT you can return it to the caller or put it into a table, so I'll leave that up to you.
If your databases are unchanging, then of course you can just write your query and maybe put it into a VIEW for convenience:
SELECT columns from database1.dbo.RefreshLog
UNION ALL
SELECT columns from database2.dbo.RefreshLog
...

and so on
But if you're saying that your databases are themselves dynamic, in other words that databases may be created or dropped over the lifetime of your project, then you could consider using the "undocumented" procedure sp_msforeachdb to build up a list of databases, and then use THAT list to build your UNION query.  Here's a quick script that captures the names of all databases that include a specific table ("Products" in the example):
IF object_id('tempdb..#DatabaseNames') IS NOT NULL
   DROP TABLE #DatabaseNames

CREATE TABLE #DatabaseNames (DatabaseName SYSNAME)

execute sp_msforeachdb @command1=
N'IF EXISTS(SELECT * FROM [?].sys.tables WHERE Name = ''Products'')
     INSERT #DatabaseNames VALUES(N''Database [?]'')'

SELECT * FROM #DatabaseNames

