CREATE PROCEDURE dbo.FindString
@search_string NVARCHAR(4000),
@database_list NVARCHAR(MAX) = NULL,
@case_sensitive BIT = 0,
@include_jobs BIT = 0,
@include_columns BIT = 0,
@include_parameters BIT = 0,
@include_system_objects BIT = 0,
@include_system_databases BIT = 0
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@init_sql NVARCHAR(MAX),
@run_sql NVARCHAR(MAX),
@dbname NVARCHAR(128),
@all_text NVARCHAR(10),
@coll_text NVARCHAR(50);
CREATE TABLE #t
(
[database] SYSNAME,
[schema] SYSNAME,
[object] SYSNAME,
[type] SYSNAME,
[create_date] DATETIME,
[modify_date] DATETIME,
[definition] NVARCHAR(MAX)
);
CREATE TABLE #j
(
[job_name] SYSNAME,
[step_id] INT,
[step_name] SYSNAME,
[create_date] DATETIME,
[modify_date] DATETIME,
[definition] NVARCHAR(MAX)
);
CREATE TABLE #cp
(
[database] SYSNAME,
[schema] SYSNAME,
[object] SYSNAME,
[type] SYSNAME,
[create_date] DATETIME,
[modify_date] DATETIME,
[param] NVARCHAR(128),
[column] NVARCHAR(128)
);
SELECT
@all_text = CASE @include_system_objects
WHEN 1 THEN N'all_' ELSE N'' END,
@coll_text = CASE @case_sensitive
WHEN 1 THEN N'COLLATE Latin1_General_BIN' ELSE N'' END;
SET @init_sql = N'SELECT
[database] = ''$db$'',
[schema] = QUOTENAME(s.name),
[object] = QUOTENAME(o.name),
[type] = o.type_desc,
o.create_date,
o.modify_date,
m.[definition]
FROM
$db$.sys.$all$sql_modules AS m
INNER JOIN
$db$.sys.$all$objects AS o
ON m.[object_id] = o.[object_id]
INNER JOIN
$db$.sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE
m.definition $coll$
LIKE N''%'' + @search_string + ''%'' $coll$;';
SET @init_sql = REPLACE(REPLACE(@init_sql,
'$all$', @all_text), '$coll$', @coll_text);
SET @search_string = REPLACE(@search_string, '''', '''''');
DECLARE c CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT QUOTENAME(d.name)
FROM
sys.databases AS d
LEFT OUTER JOIN
dbo.SplitStrings_XML(@database_list, N',') AS s
ON 1 = 1
WHERE
(
LOWER(d.name) = LOWER(s.Item)
OR NULLIF(RTRIM(@database_list), N'') IS NULL
)
AND d.database_id BETWEEN CASE @include_system_databases
WHEN 1 THEN 1 ELSE 5 END AND 32766
ORDER BY d.name;
OPEN c;
FETCH NEXT FROM c INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @run_sql = REPLACE(@init_sql, N'$db$', @dbname);
INSERT #t
EXEC sp_executesql
@run_sql,
N'@search_string NVARCHAR(4000)',
@search_string;
IF @include_columns = 1
BEGIN
SET @run_sql = N'SELECT
[database] = ''$db$'',
[schema] = QUOTENAME(s.name),
[object] = QUOTENAME(o.name),
[type] = o.type_desc,
o.create_date,
o.modify_date,
NULL,
c.name
FROM
$db$.sys.$all$columns AS c
INNER JOIN
$db$.sys.$all$objects AS o
ON c.[object_id] = o.[object_id]
INNER JOIN
$db$.sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE
c.name $coll$
LIKE N''%'' + @search_string + ''%'' $coll$;';
SET @run_sql = REPLACE(REPLACE(REPLACE(@run_sql,
'$all$', @all_text), '$coll$', @coll_text), '$db$', @dbname);
INSERT #cp
EXEC sp_executesql
@run_sql,
N'@search_string NVARCHAR(4000)',
@search_string;
END
IF @include_parameters = 1
BEGIN
SET @run_sql = N'SELECT
[database] = ''$db$'',
[schema] = QUOTENAME(s.name),
[object] = QUOTENAME(o.name),
[type] = o.type_desc,
o.create_date,
o.modify_date,
p.name,
NULL
FROM
$db$.sys.$all$parameters AS p
INNER JOIN
$db$.sys.$all$objects AS o
ON p.[object_id] = o.[object_id]
INNER JOIN
$db$.sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE
p.name $coll$
LIKE N''%'' + @search_string + ''%'' $coll$;';
SET @run_sql = REPLACE(REPLACE(REPLACE(@run_sql,
'$all$', @all_text), '$coll$', @coll_text), '$db$', @dbname);
INSERT #cp
EXEC sp_executesql
@run_sql,
N'@search_string NVARCHAR(4000)',
@search_string;
END
FETCH NEXT FROM c INTO @dbname;
END
CLOSE c;
DEALLOCATE c;
SELECT 'Objects:';
SELECT
[database],
[schema],
[object],
[type],
[definition] = CONVERT(XML, '<?query --
USE ' + [database] + ';'
+ CHAR(13) + CHAR(10) + 'GO'
+ CHAR(13) + CHAR(10) + [definition] + ' --?>'),
first_line = (DATALENGTH(abbrev_def)
-DATALENGTH(REPLACE(abbrev_def, CHAR(13), '')))/2 + 1,
create_date,
modify_date
FROM
(
SELECT
*,
[count] = (DATALENGTH([definition])
- DATALENGTH(REPLACE([definition], @search_string, '')))
/DATALENGTH(@search_string),
abbrev_def = SUBSTRING([definition], 1,
CHARINDEX(@search_string, [definition]))
FROM #t
) AS x
ORDER BY [database], [schema], [object];
IF @include_jobs = 1
BEGIN
SELECT 'Jobs:';
SET @run_sql = N'SELECT
job_name = j.name,
s.step_id,
s.step_name,
j.date_created,
j.date_modified,
[definition] = s.command
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS s
ON j.job_id = s.job_id
WHERE s.command $coll$
LIKE ''%'' + @search_string + ''%'' $coll$
ORDER BY j.name, s.step_id;';
SET @run_sql = REPLACE(@run_sql, '$coll$', @coll_text);
INSERT #j EXEC sp_executesql
@run_sql,
N'@search_string NVARCHAR(4000)',
@search_string;
SELECT
job_name,
step_id,
step_name,
[command] = CONVERT(XML, '<?query --
' + [definition] + ' --?>'),
create_date,
modify_date
FROM #j;
END
IF @include_columns = 1 OR @include_parameters = 1
BEGIN
SELECT 'Columns/parameters';
SELECT
[database],
[schema],
[object],
[type],
[param],
[column],
create_date,
modify_date
FROM #cp
ORDER BY [database], [schema], [object], [param], [column];
END
DROP TABLE #t, #j, #cp;
END
GO
|