Running a loop across a list of database without changing to the database

• Dean Jones-454305

  SSCarpal Tunnel

  Points: 4804

  More actions

  May 12, 2009 at 6:31 am

  #204599

  Hi All

  I have a problem, I need to be able to write a loop that will loop through all databases and run a stored procedure against that database in a loop, I don’t want to have to use the USE statement.

  Scenario: I have a procedure on a database called test, but I want to run the procedure against all the databases without having to change database name from Query analyzer, I want to be able to put this in a loop.

  Thanks

  Declare @sql varchar(3000)

  Set @sql = ‘Use ‘ + @dbname + ‘ exec test.dbo.SPNAme ‘ + @dbname

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  May 12, 2009 at 7:25 am

  #992873

  Try this:

  EXEC sp_msforeachdb 'Use ?; exec test.dbo.SPNAme ''?'' '

  It should work.

  -- Gianluca Sartori

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  May 12, 2009 at 8:02 am

  #992950

  You can either use the un-documented procedure "sp_MSforeachdb" or dynamic sql.

  --Using un-documented procedure sp_MSforeachdb

  EXECUTE dbo.sp_MSforeachdb 'EXECUTE [?].dbo.SomeProcedure'

  -- Using Dynamic SQL

  DECLARE @sql VARCHAR(MAX)

  SELECT @sql = ISNULL( @sql + ';' + CHAR(10), '' ) + 'EXECUTE [' + [name] + '].dbo.SomeProcedure'

  FROMsys.databases

  PRINT @sql

  --EXECUTE( @sql )

  --Ramesh
  

  ---