Thursday, May 27, 2010

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
  drop table ##tmp_sqlstatements
create table ##tmp_sqlstatements (
  Id int identity(1, 1)
, SqlStatement nvarchar(max)

sp_msforeachdb @command1 = '
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
  select  @nsql = SqlStatement
  from    ##tmp_sqlstatements
  where   Id = @currow
  exec sp_executesql @nsql

  set @currow = @currow + 1

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.

No comments:

Post a Comment