Post reply

way to run a query against all dbs?

  • April 2, 2004 at 9:21 am

    #359407

    I would like to create a report that lists all databases on a server with their corresponding Recovery Model setting.

    I know how to retrieve this information for one db at a time by executing the following using each DB_NAME:

    SELECT databasepropertyex('DB_NAME', 'recovery')

    I know there is a better way to do this but my t-sql isn't very good. I am sort of stuck and could use some help.

    Does anyone know a better way to do this?

    I'd appreciate any help.

    Thanks!

    John

     

     

     

  • April 2, 2004 at 9:59 am

    #501638

    You could use the undocumented stored procedure 'sp_msforeachdb'.

    From The Guru's Guide to Transact-SQL by Ken Henderson:

    Procedure:

    sp_msforeachdb

    @command1

    @replacechar = '?'

    [,@command2]

    [,@command3]

    [,@precommand]

    [,@postcommand]

    Purpose:

    Executes upto three commands for every database on the system. @replacechar will be replaced with the name of each database. @precommand and @postcommand can be used to direct results to a single result set.

    Example:

    EXEC sp_msforeachdb @command1='PRINT "Listing ?"', @command2 = 'USE ?; EXEC sp_dir'

    -SQLBill

  • April 2, 2004 at 10:15 am

    #501644

    sp_helpdb will also erturn this, but you have to parse the info.

  • April 2, 2004 at 1:02 pm

    #501664

    Thank you both for your time.

    I totally forgot this info was returned by sp_helpdb. That will do just fine!

    Thanks!

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

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