SQL Search Procedures and Jobs: Search Procedure or Job

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that could really hamper performance.

What does the Script do?
This script takes a search String and looks for it in all the procedures on an instance (yes instance), so if you have many databases in an instance (i.e. in my work place one Instance may have 50 to 70 databases) it will find them. If I need to find a procedure within all of these databases it becomes really cumbersome. The script will loop thru all the Databases and look for your search term. The script will also look for your term in all the jobs in that instance.

Once it finds the search term it will return the Database it is stored in, when it was created, the last time it was edited, whether it is a Procedure or a job, and if it is a job it will tell you what step in the job it’s in. Most importantly it will also provide the code so you can copy and paste it to a text editor if you need a quick view (The only drawback is the code is displayed as one line.)

The columns returned by the script are: ObjectID, Database, ObjectType or JobName, StepName, ProcName, CreationDate, ModifiedDate, Code, rn

How to Use:
Just enter a search word where indicated below. If you wish to filter based on creation time of Procedure or JOB you can enter values below. I have the 2 lines commented out here but you can change that as you see fit.

In order to filter by Object you must enter a valid Object as defined by SQL. These are the valid Objects:

AGGREGATE_FUNCTION
CHECK_CONSTRAINT
DEFAULT_CONSTRAINT
FOREIGN_KEY_CONSTRAINT
SQL_SCALAR_FUNCTION
CLR_SCALAR_FUNCTION
CLR_TABLE_VALUED_FUNCTION
SQL_INLINE_TABLE_VALUED_FUNCTION
INTERNAL_TABLE
SQL_STORED_PROCEDURE
CLR_STORED_PROCEDURE
PLAN_GUIDE
PRIMARY_KEY_CONSTRAINT
RULE
REPLICATION_FILTER_PROCEDURE
SYSTEM_TABLE
SYNONYM
SERVICE_QUEUE
CLR_TRIGGER
SQL_TABLE_VALUED_FUNCTION
SQL_TRIGGER
TABLE_TYPE
USER_TABLE
UNIQUE_CONSTRAINT
VIEW
EXTENDED_STORED_PROCEDURE

If you need to search for these Objects just use '' as the search string and it will return all the Objects of the type entered.

Any feedback would be greatly appreciated.

--
--Search words in all DBs
Declare @searchSTR varchar(4000), @database varchar(255), @sql nvarchar(4000),@sql2 nvarchar(4000)

--Set the string to look for here
Set @searchSTR = 'YOUR SEARCH TERM HERE'

IF OBJECT_ID('tempdb..#ProcList') IS NOT NULL
	Drop Table #ProcList

Create table #ProcList(
ObjectID Varchar(255),
[Database] varchar(255),
[ObjectType or JobName] varchar(255),--[JobName] varchar(255),
[StepName] varchar(255),
ProcName varchar(255),
CreationDate datetime,
ModifiedDate datetime,
Code nvarchar(max)
)
 
declare DB cursor fast_forward for 
SELECT name FROM master..sysdatabases (nolock)

open DB
fetch next from DB into @database   

while @@fetch_status = 0
begin

Set @sql = 
N'Use ['+@database+']

Insert into #ProcList
SELECT Object_ID,'''+@database+''',''SQL_STORED_PROCEDURE'',NULL,Name,Create_Date,modify_date,ltrim(OBJECT_DEFINITION(OBJECT_ID))
FROM sys.procedures (nolock)
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%'+@searchSTR+'%''

Insert into #ProcList
SELECT ID,'''+@database+''',''VIEW'',NULL,sv.Name,sv.Create_date,sv.Modify_date, ltrim(sc.[text] )
from sys.sysComments sc (nolock)
	inner join sys.views sv (nolock)
	on sv.Object_id = sc.id
where sc.[text] like ''%'+@searchSTR+'%''

Insert into #ProcList
Select Object_ID,'''+@database+''',Type_Desc,NULL,Name, Create_date,Modify_date, NULL
from sys.Objects
where name Like ''%'+@searchSTR+'%'''

Print @sql

BEGIN TRY
exec sp_executesql @sql

END TRY

BEGIN CATCH
Insert into #ProcList
SELECT '',@database,NULL,NULL,NULL,getdate(),getdate(),ERROR_MESSAGE() +'  With ErrorCode = '+ convert(varchar,ERROR_NUMBER())
GOTO Next1
END CATCH


Next1:
fetch next from DB into @database   
end 
close DB
deallocate DB

Set @sql2 = 'Insert into #ProcList
 SELECT a.Job_ID,Database_Name,Name,Step_Name,NULL,Date_Created,Date_Modified, ltrim(Command)
FROM  msdb..sysjobs a (nolock) INNER JOIN
      msdb..sysjobsteps b (nolock) ON a.job_id = b.job_id
WHERE b.command LIKE ''%'+@searchSTR+'%'''

BEGIN TRY
Print @sql2
exec sp_executesql @sql2
END TRY

BEGIN CATCH
Insert into #ProcList
SELECT '',@database,NULL,NULL,NULL,getdate(),getdate(),ERROR_MESSAGE() +'  With ErrorCode = '+ convert(varchar,ERROR_NUMBER())

END CATCH


Select * from (
SelecT *,
Row_Number() over (Partition By ObjectID order by Code Desc) rn
from #ProcList 
) a 
Where rn = 1
--And CreationDate > '2011-08-01'               --ENTER A CREATION TIME FILTER HERE IF NEEDED
--and [ObjectType or JobName] in ('USER_TABLE') --ENTER OBJECT TYPE HERE IF NEEDED
Order by [ObjectType or JobName],[Database],CreationDate

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:

Select all Open in new window

SQL Search Procedures and Jobs

Search Procedure or Job

MS SQL Server

About The Author

More Articles From ltorres321

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top MS SQL Server Experts