Post reply

Syntax of ms_foreachdb?

  • June 6, 2003 at 8:43 am

    #82633

    Does anyone have an example of using the sp ms_foreachdb? I know what it does, I just can't remember the syntax.

    TIA,

    Bobby

  • June 6, 2003 at 9:13 am

    #460206

    sp_msforeachdb 'use ?;insert into server.db.dbo.database_files_FOR_BKUP exec sp_helpfile'

    GO



    Michelle

  • June 6, 2003 at 9:25 am

    #460211

    FOR SQL Server version 6.5

    You will need to create a TEMP table as follows and execute sp_MSDBUserProfile proc.

    Create Table #SQLOLEDbUserProfile (dbid int NOT NULL PRIMARY KEY,

    profilebits int NOT NULL)

    Exec Master..sp_MSDBUserProfile 'init'

    Exec sp_msforeachdb 'Use ? Select db_id(), ''?'' '

    For vesrsions 7.0 and greater you can simply exec. the proc as follows

    Exec sp_msforeachdb 'Use ? Select db_id(), ''?'' ' OR

    Exec sp_msforeachdb 'Use ? Select * From Authors Where au_id is not null'

    MW


    MW

  • June 6, 2003 at 10:29 am

    #460225

    Thanks to all who responded.

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

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