Exec scripts in a group of databases

Question

Post reply

Exec scripts in a group of databases

Bill Talada

SSChampion

Points: 11954
More actions
December 15, 2009 at 4:18 pm

#214747

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

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

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

Kuido Külm SSC-Addicted Points: 493 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

akljfhnlaflkj

SSC Guru

Points: 76202

May 23, 2016 at 7:15 am

#1880756

Thanks for the script Bill.

mateen 5756 Newbie Points: 8 More actions · Answer 4

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