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
@Queryvarchar(4000),
@IsLikevarchar(1000) = '%',
@IsNotLikevarchar(1000) = '',
@List_Onlybit = 0,
@Script_Onlybit = 0,
@Replacevarchar(255) = '#DBName#'
AS
BEGIN
/*
**  Description:
**  Runs a query in all user databases based on LIKE pattern.
******************************************************************************************
**  DateNameNotes Runtime
******************************************************************************************
**  11/14/02Mike 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 @CurDBsysname,
@SQLTextNVarchar(4000),
@PrintStringvarchar(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