Exec scripts in a group of databases

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by William Talada » Exec scripts in a group of databases

Exec scripts in a group of databases

Rate Topic

Display Mode

Topic Options

Author

Message

Bill Talada

Posted Tuesday, December 15, 2009 4:18 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:31 AM
Points: 65, Visits: 443

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

Post #834793

Kuido Külm

Posted Thursday, December 31, 2009 1:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 15, 2012 1:12 AM
Points: 185, Visits: 43

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

Post #840709

maciej.krasuski

Posted Wednesday, January 13, 2010 6:00 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, December 15, 2011 6:57 AM
Points: 72, Visits: 174

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.

Post #846816

« Prev Topic | Next Topic »

Permissions