Running a Query against all databases in the Instance

• Andrew-331669

  SSC-Addicted

  Points: 442

  More actions

  October 5, 2006 at 4:49 pm

  #67756

  Hello All

  I am wanting to run some SQL code, stored procedure, on each and every database in a specific instance. I do not want to place this stored procedure on each and every database. I need to make modifications to the code in the sproc at times, and then run it on all the databases by selecting the database name from the sys.databases table, then make the code run for each database. Kind of like being able to change the USE each and every time for each database name that is listed.

  Does anyone have any thoughts on how I may be able to accomplish this. I am not too concerned with performance. I am the only person on this particular database server.

  Thanks

  Andrew SQLDBA

• Aaron Ingold

  SSCrazy Eights

  Points: 9070

  More actions

  October 5, 2006 at 4:54 pm

  #664477

  Use sp_msforeachdb... Example:

  exec sp_msforeachdb 'select * from sysobjects'

• Aaron Ingold

  SSCrazy Eights

  Points: 9070

  More actions

  October 5, 2006 at 4:55 pm

  #664478

  Rather... (going a little too quickly)

  exec sp_msforeachdb 'select * from ?.dbo.sysobjects'

  it replaces ? with the name of the database.