USE MASTER
GO
IF OBJECT_ID('sp_proc_find_DBList') IS NULL
EXEC ('CREATE PROCEDURE sp_proc_find_DBList AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE sp_proc_find_DBList
@DBList VARCHAR(MAX) = NULL,
@PatternList VARCHAR(MAX) = NULL,
@PatternDelimiter VARCHAR(256) = ',',
@ReplacePatternList VARCHAR(MAX) = NULL,
@TypeList VARCHAR(256) = NULL,
@TypeDelimiter VARCHAR(60) = ',',
@SchemaList VARCHAR(MAX) = NULL,
@SchemaDelimiter VARCHAR(256) = ',',
@NameList VARCHAR(MAX) = NULL,
@NameDelimiter VARCHAR(256) = ',',
@IgnoreSchemaList VARCHAR(MAX) = 'cdc,sys,Metadata,tTaxMat,tTaxProp,tTransMat,tTransProp',
@IgnoreSchemaDelimiter VARCHAR(30) = ',',
@SQL VARCHAR(MAX) = NULL OUTPUT,
@FilePath VARCHAR(256) = NULL,
@PrintSQL BIT = 1,
@PrintDynamicSQL BIT = 0,
@ExecSQL BIT = 1,
@PrintAnsi BIT = 1,
@PrintHeader BIT = 1,
@ReturnAlter BIT = 0,
@WildChar BIT = 0,
@CaseSensitive BIT = 0,
@SelectObjectList BIT = 0
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @PatternList IS NULL
OR @DBList IS NULL
BEGIN
PRINT '
/*
@DBList : ''Diablo,Corestore,etc'', -- Comma delimited database lit
@PatternList : WILL BE USED IN LIKE COMPARISON
@Type : NULL for ALL or FN, IF, P, TF, TR, V (list below)
@Schema : NULL for ALL or ''%trans%''
@Name : NULL for ALL or ''%Extract%''
@FilePath : NULL, -- or ''C:\Temp\sp_proc_find.sql'', -- remember local to sql server not to your desktop
@ReturnAlter : RETURNS AN ALTER SCRIPT INSTEAD
@PrintAnsi : Scripts SET QUOTED_IDENTIFIER AND ANSI_NULLS settings
@PrintHeader : Prints short header that has the proc name and matched line
@WildChar : searches with LIKE 0 searches with CHARINDEX
-- Type TypeName
-- ---- ------------------------------------
-- FN SQL scalar function
-- IF SQL inline table-valued function
-- P Stored procedure
-- TF Table Valued Function
-- TR SQL DML trigger
-- V View
/*
DECLARE @DBList VARCHAR(MAX),
@SQL VARCHAR(MAX),
@FilePath VARCHAR(256) = ''C:\Temp\'' + REPLACE(@@SERVERNAME, ''\'', ''_'') + ''.SQL''
SELECT @DBList = Util.dbo.StringConcat(name, '','')
FROM sys.databases (NOLOCK)
WHERE name NOT IN (''master'', ''tempdb'', ''model'', ''msdb'')
AND name NOT LIKE ''LiteSpeed%''
AND name NOT LIKE ''ReportS%''
AND is_read_only = 0
AND collation_name = ''SQL_Latin1_General_CP1_CI_AS''
'
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_proc_find_DBList',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
PRINT '
*/'
RETURN
END
DECLARE @SQLExec NVARCHAR(MAX)
SELECT @SQLExec = Util.dbo.StringConcat('
USE [' + d.NAME + ']
EXEC sp_proc_find
@PatternList = ' + ISNULL('''' + REPLACE(@PatternList, '''', '''''') + '''', 'NULL') + ',
@PatternDelimiter = ' + ISNULL('''' + REPLACE(@PatternDelimiter, '''', '''''') + '''', 'NULL') + ',
@ReplacePatternList = ' + ISNULL('''' + REPLACE(@ReplacePatternList, '''', '''''') + '''', 'NULL') + ',
@TypeList = ' + ISNULL('''' + REPLACE(@TypeList, '''', '''''') + '''', 'NULL') + ',
@TypeDelimiter = ' + ISNULL('''' + REPLACE(@TypeDelimiter, '''', '''''') + '''', 'NULL') + ',
@SchemaList = ' + ISNULL('''' + REPLACE(@SchemaList, '''', '''''') + '''', 'NULL') + ',
@SchemaDelimiter = ' + ISNULL('''' + REPLACE(@SchemaDelimiter, '''', '''''') + '''', 'NULL') + ',
@NameList = ' + ISNULL('''' + REPLACE(@NameList, '''', '''''') + '''', 'NULL') + ',
@NameDelimiter = ' + ISNULL('''' + REPLACE(@NameDelimiter, '''', '''''') + '''', 'NULL') + ',
@IgnoreSchemaList = ' + ISNULL('''' + REPLACE(@IgnoreSchemaList, '''', '''''') + '''', 'NULL') + ',
@IgnoreSchemaDelimiter = ' + ISNULL('''' + REPLACE(@IgnoreSchemaDelimiter, '''', '''''') + '''', 'NULL') + ',
@SQL = @SQL OUTPUT,
@PrintAnsi = ' + ISNULL(CAST(@PrintAnsi AS VARCHAR), 'NULL') + ',
@PrintHeader = ' + ISNULL(CAST(@PrintHeader AS VARCHAR), 'NULL') + ',
@PrintSQL = 0,
@PrintUseDB = 1,
@WildChar = ' + ISNULL(CAST(@WildChar AS VARCHAR), 'NULL') + ',
@ReturnAlter = ' + ISNULL(CAST(@ReturnAlter AS VARCHAR), 'NULL') + ',
@SelectObjectList = ' + ISNULL(CAST(@SelectObjectList AS VARCHAR), 'NULL') + ',
@CaseSensitive = ' + ISNULL(CAST(@CaseSensitive AS VARCHAR), 'NULL'), '
')
FROM UTIL.dbo.ParseDelimited(REPLACE(REPLACE(@DBList, '[', ''), ']', ''), ',') a
INNER JOIN sys.databases d (NOLOCK) ON d.name = LTRIM(a.Field)
IF @PrintDynamicSQL = 1
BEGIN
PRINT '/* -- SCRIPT TO LOOP THROUGH ALL DATABASES START
DECLARE @SQL VARCHAR(MAX)'
EXEC Util.dbo.PrintLargeText
@SQLExec
PRINT 'EXEC Util.dbo.PrintLargeText @SQL
-- SCRIPT TO LOOP THROUGH ALL DATABASES END */'
END
IF @ExecSQL = 1
AND @SQLExec <> ''
BEGIN
EXEC sys.sp_executesql
@SQLExec,
N'@SQL VARCHAR(MAX) OUTPUT',
@SQL OUTPUT
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
IF @FilePath <> ''
SELECT ReturnVal,
MESSAGE,
@FilePath AS [FileName],
'Saved to ' + @FilePath AS Info
FROM Util.FS.AppendAllTextToFile(@FilePath, @SQL, 1)
END
GO
EXEC sys.sp_ms_marksystemobject
sp_proc_find_DBList
GO