CODEWITHSUNDEEP
sqlsql-serverdatabase
Simon Johnson
Simon Johnson

Reputation: 33

Querying the same table for a list of databases in MS SQL Server

This is my first time posting on SO, so please go easy!

I'm attempting to write a SQL script that queries the same table for a list of databases in a single SQL Server instance.

I have successfully queried the list of databases that I required using the following, and inserting this data into a temp table.

Select name Into #Versions
From sys.databases
Where name Like 'Master%'

Master is suffixed with numerical values to identify different environments.

Select * From #Versions

Drop Table #Versions

The table name I am trying to query, is the same in each of the databases, and I want to extract the newest value from this table and insert it into the temp table for each of the database names returned.

I have tried researching this but to no avail. I am fairly comfy with SQL but I fear I could be out of my depth here.

Upvotes: 3

Views: 322

Answers (2)

Szymon
Szymon

Reputation: 43023

You can do the following. Once you have the list of your databases, you can build up the query (you need to edit it for your purpose).

Select name Into #Versions
From sys.databases
Where name Like 'test%'

declare @sql as varchar(max) = ''
select @sql = @sql + 'INSERT INTO sometable SELECT TOP 1 * FROM ' + name + '..sourcetable ORDER BY somedate DESC; '
FROM #Versions

exec (@sql)

Drop Table #Versions

Upvotes: 2

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Look at The undocumented sp_MSforeachdb procedure and here

Upvotes: 0

Related Questions