These scripts allow you to search SQL Server stored procedures, jobs and table columns to find a specific word. In particular I’ve found the stored procedure / job script useful for looking for pieces of code related to an issue, or updates I want to make to parts of scripts. The table-searching script I use more rarely, but handy in some rare cases the Polaris CHM file isn’t helpful (and the results are alphabetized, where my CHM reader won’t return sorted results from a search).
Admin - Search Stored Proc & Job for Text.sql (522 Bytes)
DECLARE @Text VARCHAR(max) = ''
SELECT DISTINCT
o.name AS Object_Name,
o.type_desc
FROM sys.sql_modules m
INNER JOIN
sys.objects o
ON m.object_id = o.object_id
WHERE m.definition Like '%' + @Text + '%'
-- search the jobs for a specific text
SELECT SERVERPROPERTY('SERVERNAME') as [InstanceName],
j.job_id,
j.name,
js.step_id,
js.command,
j.enabled
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobsteps js
ON js.job_id = j.job_id
WHERE js.command LIKE '%' + @Text + '%'
Admin - Search Table Column Names for Text.sql (680 Bytes)
USE POLARIS
-- Search column and table names to find specific text
-- The version below checks for text in 2 separate columns, and a table name; table 'c2' can be commented out to search for only 1 column name
SELECT c.name AS 'ColumnName'
,c2.name As '2nd ColumnName'
,t.name AS 'TableName'
FROM sys.columns c
INNER JOIN sys.tables t
ON c.object_id = t.object_id
INNER JOIN sys.columns c2
ON c2.object_id = t.object_id
WHERE c.name LIKE '%Auth%ID%' -- column name filter
AND c2.Name LIKE '%Bib%ID%' -- second column name filter
AND t.name not like '%Import%' -- table name filter
ORDER BY TableName
,ColumnName;