<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

SQL Search Procedures and Jobs

Published on
8,814 Points
2,814 Views
Last Modified:
Approved
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

Open in new window

0
Comment
Author:Leo Torres
0 Comments

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Join & Write a Comment

Viewers will learn how the fundamental information of how to create a table.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month