Execute a query on ALL databases? sp_MsForEachDB might do the trick!

Ever wanted to execute a query on ALL databases available on 1 server?

sp_msforeachdb @command1 = '

select  *

from    [?].sys.database_files

order by name'

the question mark will be replaced with the database name, so generating some SQL for each DB that exists will be easy. I used this one to shrink some large transaction logs




if object_id('##tmp_sqlstatements') is not null

begin

  drop table ##tmp_sqlstatements

end

create table ##tmp_sqlstatements (

  Id int identity(1, 1)

, SqlStatement nvarchar(max)

)

go

sp_msforeachdb @command1 = '

insert

into    ##tmp_sqlstatements

select  ''use [?];

          dbcc shrinkfile('''''' + name + '''''')''

from    [?].sys.database_files

where   type_desc = ''log''

order by name'

declare @totrow int

      , @currow int

      , @nsql nvarchar(max)

select  @totrow = count(Id)

      , @currow = 1

from    ##tmp_sqlstatements

while @currow <= @totrow

begin

  select  @nsql = SqlStatement

  from    ##tmp_sqlstatements

  where   Id = @currow

 

  exec sp_executesql @nsql

  set @currow = @currow + 1

end

drop table ##tmp_sqlstatements

It executes all the statements at once, so it can be used as a part of a maintenance plan. If creating more disk space is the issue for executing this script, make sure that you put the recovery mode into simple. But of course only when you don't need the transaction logs!!

This is very easy to do :

select  replace('alter database [[NAME]] set recovery simple ', '[NAME]', name)

from    sys.databases

where   recovery_model_desc = 'full'

order by name

This will give a result of SQL statements to execute.