Post reply

Loop Through DBs and Alter them

  • March 18, 2005 at 5:19 am

    #162902

    Hi,

    I'm confident someone here can guide me in the right direction... I need to write a script that will loop through all the databases on our server and ALTER to READ/WRITE only those dbs who's names start with the prefix "tg_" and do NOT have the word "training" in them.  Then we will run another set of statements and then loop through the dbs again to change them back to READ ONLY.  

    I'm guessing I have to use Dynamic SQL to do this, but I've never used Dynamic SQL before.  Can anyone help me out?

    Thanks in advance!!!

  • March 18, 2005 at 9:25 am

    #547812

    -- Make them read only

    exec sp_MSforeachdb 'if (''?'' like ''tg/_%'' escape ''/'' and ''?'' not like ''%training%'' ) begin  exec(''ALTER DATABASE [?] SET READ_ONLY'') end'

    -- Make them read write

    exec sp_MSforeachdb 'if (''?'' like ''tg/_%'' escape ''/'' and ''?'' not like ''%training%'' ) begin  exec(''ALTER DATABASE [?] SET READ_ONLY'') end'

    hth


    * Noel

  • March 18, 2005 at 9:33 am

    #547818

    Shouldn't the second exec read? :

    ALTER DATABASE [?] SET READ_WRITE

  • March 18, 2005 at 9:40 am

    #547821

    Yep,

    Copy and Paste effect

    Thanks Remi

    rmazzol,  here is the corrected statement

    -- Make them read write

    exec sp_MSforeachdb 'if (''?'' like ''tg/_%'' escape ''/'' and ''?'' not like ''%training%'' ) begin  exec(''ALTER DATABASE [?] SET READ_WRITE'') end'


    * Noel

  • March 21, 2005 at 4:58 am

    #547971

    Thanks! Works great!

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

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