CODEWITHSUNDEEP
sql-servert-sql
MPJ567
MPJ567

Reputation: 553

Select over multiple databases TSQL

I have a single select statement I'd like to run against multiple databases simultaneously:

select LastName from TABLE where LastName like '%eloph'

All the databases I want to select from contain the same table, with identical structures, but there are over 300 of them. How can I avoid writing that same query 300 times?

Upvotes: 0

Views: 2144

Answers (1)

Mike
Mike

Reputation: 1667

You can just append the database name in front of the table name. If you want all the data returned as one result set then use UNION ALL to append the results

Select LastName from dbname1.dbo.TABLE where LastName like '%eloph'
UNION ALL 
Select LastName from dbname2.dbo.TABLE where LastName like '%eloph'
UNION ALL
Select LastName from dbname3.dbo.TABLE where LastName like '%eloph'
UNION ALL
Select LastName from dbname4.dbo.TABLE where LastName like '%eloph'

Keep adding Union all and select statements.

Or if you adverse to editing, this should generate most of what you need.

It will generate the sql for you, just copy it and remove the last UNION ALL

SELECT 'Select LastName from ' + name + '.dbo.TABLE where LastName like ''%eloph''
UNION ALL ' 
FROM sys.databases

Note the doubled single quotes

Upvotes: 2

Related Questions