-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Written by Kimberly L. Tripp - all rights reserved.
-- 
-- For more scripts and sample code, check out 
-- 	http://www.SQLSkills.com
--
-- Disclaimer - Thoroughly test this script, execute at your own risk.
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-- Execute this whole script to create the sp_ExecuteInDatabase stored procedure in Master.
-- Best Viewed with Courier New 12pt. and Tabs saved as 4 spaces not 8. (Tools, Options, Editor)

-- To use the sp_ExecuteInDatabase procedure once created use:
-- sp_ExecuteInDatabase 

USE master
go

IF OBJECTPROPERTY(object_id('sp_ExecuteInDatabase'), 'IsProcedure') = 1
	DROP PROCEDURE dbo.sp_ExecuteInDatabase
go

CREATE PROCEDURE dbo.sp_ExecuteInDatabase
(
	@CommandToExecute	nvarchar(3500)	= NULL,
	@DatabaseName		sysname			= 'ALL'
)		
AS
SET NOCOUNT ON

-- Written by Kimberly L. Tripp of SYSolutions, Inc.
-- For more code samples go to http://www.sqlskills.com
-- NOTE: This is a quick version - NO ERROR HANDLING and a
-- minimal amount of testing...really just a sample to get
-- you started. PLEASE REVIEW AND TEST before implementing
-- this (well, or anything!!!!) in production.
-- USE AT YOUR OWN RISK!
-- PLEASE EMAIL ME with additional versions/modifications, etc.
-- THANKS!!!
-- Kimberly@SQLskills.com

-- Avoiding sysdatabases for fear it may no longer exist in future
-- releases...
--
-- Also, the assumption is made that IF you want to process something
-- in every database you have access to every database. This script
-- has only been tested while running under the context of a system
-- administrator.

-- This next table was "leveraged" from reviewing the code in 
-- sp_helpdb. To see the code in a system procedure use:
--		sp_helptext sp_helpdb

CREATE TABLE #HelpDBOutput
(
	dbname 		sysname	NOT NULL,
	dbsize		nvarchar(13) null,
	owner 		sysname	null,
	dbid		smallint null,
	created 	nvarchar(11) null,
	dbdesc		nvarchar(600) null,
	cmptlevel	tinyint null
)

IF @DatabaseName <> 'ALL'
BEGIN
	IF DATABASEPROPERTYEX(@DatabaseName, 'COLLATION') IS NULL
	BEGIN
		RAISERROR ('Database does not exist. Please supply a valid database name or ''ALL'' to process all databases. ''ALL'' is the default.', 16, -1)
		RETURN
	END
	ELSE
	INSERT #HelpDBOutput (dbname)
		VALUES(@DatabaseName)
END
ELSE
	INSERT #HelpDBOutput EXEC sp_helpdb

DECLARE @DBToProcessName		sysname,
		@ActualScanDensity		numeric(10,2),
		@InformationalOutput	nvarchar(4000),
		@StartTime				datetime,
		@EndTime				datetime,
		@ExecString				nvarchar(4000)

DECLARE DatabaseList CURSOR FAST_FORWARD FOR 
	SELECT dbname FROM #HelpDBOutput

OPEN DatabaseList 

FETCH NEXT FROM DatabaseList 
	INTO @DBToProcessName

WHILE (@@fetch_status <> -1)
BEGIN
	BEGIN
		PRINT replicate('*', 60)
		SELECT @InformationalOutput = N'***** Processing the ' + @DBToProcessName + N' Database ' 
		PRINT @InformationalOutput 
		PRINT replicate('*', 60)
		SELECT @StartTime = getdate()
		SELECT @ExecString = N'USE ' + QUOTENAME(@DBToProcessName, ']') + N' ' + @CommandToExecute
		SELECT @ExecString AS 'Command to be executed'
		EXEC(@ExecString)
		SELECT @EndTime = getdate()
		PRINT ' '
		PRINT replicate('*', 60)
		SELECT @InformationalOutput = N'Total Time to process ' + @DBToProcessName + N' = ' + convert(nvarchar, datediff(ms, @StartTime, @EndTime)) + N' ms'
		PRINT @InformationalOutput 
		PRINT replicate('*', 60)
	END
	PRINT N' '
	FETCH NEXT FROM DatabaseList 
		INTO @DBToProcessName
END
PRINT N' '
SELECT @InformationalOutput = N'***** All Databases have been processed.  ***** ' 
PRINT @InformationalOutput 
DEALLOCATE DatabaseList 
go

-- EXEC sp_ExecuteInDatabase N'exec sp_RebuildIndexes'