Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Exec scripts in a group of databases Expand / Collapse
Author
Message
Posted Tuesday, December 15, 2009 4:18 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:23 PM
Points: 157, Visits: 981
Comments posted to this topic are about the item Exec scripts in a group of databases
Post #834793
Posted Thursday, December 31, 2009 1:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Wednesday, January 13, 2010 6:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 11:48 PM
Points: 72, Visits: 190
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 »

Add to briefcase

Permissions Expand / Collapse