SQL Server 2012- Using sp_msforeachdb

Question

I want to run a SELECT statement on only some specific databases. The list of databases is returned by this query:

DECLARE @OneWeekAgo DATETIME
SELECT @OneWeekAgo = DATEADD(week,-1,GETDATE())

select distinct DB_NAME(database_id) DatabaseName 
into #temp
from sys.dm_db_index_usage_stats
where DB_NAME(database_id) like 'TTT[_][a-z]%'
  and DB_NAME(database_id) not like '%test%' 
  and last_user_update > @OneWeekAgo

Now on all of these databases returned, I want to run a simple query:

SELECT * 
FROM TTT_Clients 
WHERE country like 'SWEDEN'

How do I do that? I get errors in the "IN (SELECT DISTINCT...)" line using something like this:

exec sp_msforeachdb ' use [?] IF  ''?'' in (select distinct DB_NAME(database_id) DatabaseName 
                                            from sys.dm_db_index_usage_stats
                                            where DB_NAME(database_id) like  ''TTT[_][a-z]%'' 
                                              and DB_NAME(database_id) not like ''%test%'') 
BEGIN
    SELECT * FROM TTT_Clients WHERE country like ''SWEDEN''
END

Darren Kopp · Accepted Answer · 2015-04-30 23:49:27Z

1

You didn't specify the error, and I don't know this for sure, but I'm guessing that sys.dm_db_index_usage_stats returns the same information regardless of database you are using (server wide view).

I think you want something like this...

exec sp_msforeachdb ' use [?];
IF ('[?]' NOT LIKE ''%test%'' AND EXISTS(SELECT * FROM sys.tables WHERE name LIKE ''TTT[_][a-z]%''))
BEGIN
    SELECT * FROM TTT_Clients WHERE country like ''SWEDEN''
END
'

Re-using your filters, I don't know if they are correct or not. Basically we are checking if the table in question exists in the database. Since you are only selecting from TTT_Clients I would suggest just filtering WHERE name = ''TTT_Clients'' rather than that regex that just matches it.

answered Apr 30, 2015 at 23:49

Darren Kopp

77.8k9 gold badges81 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server 2012- Using sp_msforeachdb

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related