Exec scripts in a group of databases

  • Bill Talada

    SSChampion

    Points: 11956

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

  • Kuido Külm

    SSC-Addicted

    Points: 493

    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

  • maciej.krasuski

    SSC Journeyman

    Points: 96

    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.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script Bill.

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

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