DBA_EverywhereRun

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DBA_EverywhereRun]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DBA_EverywhereRun]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Create Procedure DBA_EverywhereRun
	@Query		varchar(4000),
	@IsLike		varchar(1000) = '%',
	@IsNotLike	varchar(1000) = '',
	@List_Only	bit = 0,
	@Script_Only	bit = 0,
	@Replace	varchar(255) = '#DBName#'
AS
BEGIN
/*
**  Description:
**  Runs a query in all user databases based on LIKE pattern.
******************************************************************************************
**  Date	Name		Notes 				Runtime
******************************************************************************************
**  11/14/02	Mike Wallace    Version 1.0
**
*******************************************
** USAGE
** Substitute #DBName# for where you want the name of the database
** i.e. 'Select * from #DBName#.dbo.sysobjects where type = ''u'''
**
** @IsLike and @IsNotLike take standard LIKE patterns
**
** @List_Only is for debugging the LIKE params.  Setting this on gives a list
** of what databases will be affected without executing the @Query
**
** @Script_Only gives only the script of what would be executed without executing
** the @Query.  Also adds in GOs and Use statements.  Useful for DDL statements
** like Create Procedure.
**
** @Replace is for if you have to change the default replacement holder.
**
** Usage Examples:
**
	Exec DBA_EverywhereRun 'Select count(*) as ''RowCount'' from sysobjects'
	--Script only
	Exec DBA_EverywhereRun 'Select count(*) as ''RowCount'' from sysobjects', '%', '', 0, 1
	--List only
	Exec DBA_EverywhereRun 'Select count(*) as ''RowCount'' from sysobjects', '%', '', 1
*/

Declare @CurDB		sysname,
	@SQLText	NVarchar(4000),
	@PrintString	varchar(1000)



Declare  AACurs Cursor Fast_Forward
	For Select name
	from master.dbo.sysdatabases
	where name like @IsLike
	and name not like @IsNotLike
	and name not in ('master','model','msdb','tempdb')
	order by name

Open AACurs

Fetch Next  from AACurs into  @CurDB

While  @@Fetch_Status = 0
	Begin
	Set @CurDB = '[' + LTrim(RTrim(@CurDB)) + ']'
	If @Script_Only = 0
		Begin
		If @List_Only = 0
			Print '-----------------------------------------'
		Print @CurDB
		If @List_Only = 0
			Print '-----------------------------------------'
		End
	Set @SQLText = Replace(@Query, @Replace, @CurDB)
	If @Script_Only = 1 and @List_Only = 0
		Begin
		Set @PrintString = 'Use ' + @CurDB
		Print @PrintString
		Set @PrintString = 'Print ''-----------------------------------------'''
		Print @PrintString
		Set @PrintString = 'Print ''' + @CurDB + ''''
		Print @PrintString
		Set @PrintString = 'Print ''-----------------------------------------'''
		Print @PrintString
		Print 'GO'
		Print @SQLText
		Print 'GO'
		End
	Else
		Begin
		If @List_Only = 0
			exec sp_executesql @SQLText
		End

	Fetch Next  from AACurs into  @CurDB
	End
Close AACurs
Deallocate AACurs
END
GO