Post reply

Running a Query against all databases in the Instance

  • 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

  • October 5, 2006 at 4:54 pm

    #664477

    Use sp_msforeachdb... Example:

    exec sp_msforeachdb 'select * from sysobjects'

  • 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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply