SQL Search Procedures and Jobs

Leo TorresSQL Developer
CERTIFIED EXPERT
Published:
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
3,112 Views
Leo TorresSQL Developer
CERTIFIED EXPERT

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.