Exec scripts in a group of databases

  • Comments posted to this topic are about the item Exec scripts in a group of databases

  • You can use USE statement in stored procedure in dynamical SQL like

    CERATE PROCEDURE [dbo].[USE_PROOV_KUIDO_S]

    @baas NVARCHAR(128)=NULL

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @er NVARCHAR(1000)

    SET @er='USE ['+ISNULL(@baas,DB_NAME())+'];SELECT NAME FROM SYSUSERS WHERE ISSQLUSER=1 AND HASDBACCESS=1'

    EXEC (@er)

    END

    EXECUTE [dbo].[USE_PROOV_KUIDO_S] @baas='MSDB'

    EXECUTE [dbo].[USE_PROOV_KUIDO_S] @baas=NULL

  • You can also use undocumented stored procedure sp_msforeachdb. It will execute your code for all databases. Then you can add condition where you will filter unnecessary db. If you have a lot of databases it's the better choice.

  • Thanks for the script Bill.

  • Hi William Talada

    I liked the script and has created a template to my need as

    if db_name() not in ('payfknitfab','payfNova','payfd53_sql','payfa33_cont','payfjkt','payfgrmn_b26','payfgrmn_sql','payfNova_cont'

    ,'payho_sql','payfd53_cmpl','payfHO','payfd53_cont')

    use payfknitfab

    else if db_name() = 'payfknitfab' use payfNova

    else if db_name() = 'payfNova' use payfd53_sql

    else if db_name() = 'payfd53_sql' use payfa33_cont

    else if db_name() = 'payfa33_cont' use payfjkt

    else if db_name() = 'payfjkt' use payfgrmn_b26

    else if db_name() = 'payfgrmn_b26' use payfgrmn_sql

    else if db_name() = 'payfgrmn_sql' use payfNova_cont

    else if db_name() = 'payfNova_cont' use payho_sql

    else if db_name() = 'payho_sql' use payfd53_cmpl

    else if db_name() = 'payfd53_cmpl' use payfHO

    else if db_name() = 'payfHO' use payfd53_cont

    else if db_name() = 'payfd53_cont' use payfknitfab --Circular

    -- scripts to execute in database group

    select db_name() from sys.tables

    It would be great, if you could design a handy code which will create the

    script against a variable containing list of databases.

    declare @dbname varchar(max)

    set @dbname ='payfknitfab','payfNova','payfd53_sql','payfa33_cont','payfjkt','payfgrmn_b26','payfgrmn_sql','payfNova_cont'

    ,'payho_sql','payfd53_cmpl','payfHO','payfd53_cont'

    then it should create the script

    use payfknitfab

    else if db_name() = 'payfknitfab' use payfNova

    else if db_name() = 'payfNova' use payfd53_sql

    else if db_name() = 'payfd53_sql' use payfa33_cont

    else if db_name() = 'payfa33_cont' use payfjkt

    else if db_name() = 'payfjkt' use payfgrmn_b26

    else if db_name() = 'payfgrmn_b26' use payfgrmn_sql

    else if db_name() = 'payfgrmn_sql' use payfNova_cont

    else if db_name() = 'payfNova_cont' use payho_sql

    else if db_name() = 'payho_sql' use payfd53_cmpl

    else if db_name() = 'payfd53_cmpl' use payfHO

    else if db_name() = 'payfHO' use payfd53_cont

    else if db_name() = 'payfd53_cont' use payfknitfab --Circular

    Thanks

     

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

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