December 15, 2009 at 4:18 pm
Comments posted to this topic are about the item Exec scripts in a group of databases
December 31, 2009 at 1:32 am
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
January 13, 2010 at 6:00 am
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.
May 23, 2016 at 7:15 am
Thanks for the script Bill.
March 27, 2021 at 7:39 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy