Code for searching objects among databases and jobs

 

1. Among databases

-- Run on the SVANWARSQL02

DECLARE @SearchString VARCHAR(255)

DECLARE @tblName    VARCHAR(300)      -- each table from  select * from StagingData.dbo.stgETL0Cleanup  delete from StagingData.dbo.stgETL0Cleanup where searchstr ='ch_ivr_audit'
                           
DECLARE @db_name1    VARCHAR(300)
DECLARE @db_name2    VARCHAR(300)
DECLARE @sql        VARCHAR(500)
DECLARE @sql1       VARCHAR(500)
DECLARE @counter    INT
DECLARE @error_num  INT

DECLARE @name VARCHAR(50)

SET @counter = 0
--set @name = 'blabla'
--select searchstr,@name from StagingData.dbo.stgETL0Cleanup

TRUNCATE TABLE StagingData.dbo.ETL0_SearchObjectResults

IF NOT EXISTS(SELECT * FROM StagingData..sysobjects WHERE ID = OBJECT_ID('StagingData.dbo.ETL0_SearchObjectResults'))
   BEGIN
    CREATE TABLE StagingData.dbo.ETL0_SearchObjectResults
        (   
        sp_name                        VARCHAR(300),
        dbName                        VARCHAR(300),
        tblName                        VARCHAR(300),
        ID                            VARCHAR(1000)
        )
    END
   
TRUNCATE TABLE StagingData.dbo.ETL0_lkpResults   
   
IF NOT EXISTS(SELECT * FROM StagingData..sysobjects WHERE ID = OBJECT_ID('StagingData.dbo.ETL0_lkpResults'))
   BEGIN
    CREATE TABLE StagingData.dbo.ETL0_lkpResults
        (   
        dbName                        VARCHAR(300),
        tblName                        VARCHAR(300),
        ID                            VARCHAR(1000)
        )     
   END 
                   
DECLARE tbl_cursor CURSOR FOR
SELECT searchstr
FROM StagingData.dbo.stgETL0Cleanup                   

                                                       
DECLARE db_cursor CURSOR FOR 
SELECT name                 -- get the list of all databases on this server
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerExternal','ReportServerExternalTempDB','ReportServerTempDB',
'Strata','TelcoReconciliation','TestForReplication','VanBMCProduction_Backup','MapInfo','RepMD','CommonEvent','CommonEvent_2011Q1',
'CommonEvent_2011Q2','CommonEvent_2011Q3','QuestSoftware','QuestWorkDatabase','ProdAdminDB_Backup','EventData_Detail_cw','Chr_Utils_WrkDB',
'VanReplicaOld'
) 
ORDER by name
   
   
    OPEN db_cursor

 
FETCH NEXT FROM db_cursor INTO @name

While @@FETCH_STATUS = 0
BEGIN
    OPEN tbl_cursor
    FETCH NEXT FROM tbl_cursor INTO @tblname
   
        While @@FETCH_STATUS = 0
            BEGIN   
           
                SET @counter = @counter + 1
                       
                SET @db_name1 =@name+'.dbo.'+'syscomments'
                SET @db_name2 =@name+'.dbo.'+'sysobjects'      
               

                SELECT @sql =                                        
                'SELECT DISTINCT '+'o.name,'+CONVERT(varchar(1000),@counter)+' From '+@db_name1+' s JOIN'+' '+@db_name2+' o ON s.id = o.id '
               
                SELECT @sql = @sql+' WHERE  s.text like '+'''%'+@tblname+'%'''
                --SELECT @sql = @sql+' WHERE  s.text like '+'''%'+@name+'%'''   --->>>>> individual search, change the name between % %
                SELECT @sql ='INSERT INTO StagingData.dbo.ETL0_SearchObjectResults (sp_name,ID)  '+@sql   
                EXEC(@sql)
               
                Select @error_num = @@ERROR               
                IF @error_num <> 0  Print @sql
               
                SELECT @sql1 = 'SELECT '''+@name+''','''+@tblName+''','+CONVERT(varchar(1000),@counter)
                SELECT @sql1 = 'INSERT INTO StagingData.dbo.ETL0_lkpResults(dbName,tblName,ID) '+@sql1
       
                EXEC(@sql1)
               
                Select @error_num = @@ERROR
                IF @error_num <> 0  Print @sql
               
               
                FETCH NEXT FROM tbl_cursor INTO @tblname
               
            END
        Close tbl_cursor

       
    FETCH NEXT FROM db_cursor INTO @name
END   

DEALLOCATE tbl_cursor
Close db_cursor
DEALLOCATE db_cursor

--select distinct l.tblName,l.dbName,r.sp_name from StagingData.dbo.ETL0_SearchObjectResults r join StagingData.dbo.ETL0_lkpResults l on r.ID = l.ID order by l.tblName,l.dbName,r.sp_name

 

--SELECT DISTINCT o.name, 'Chr_Utils','ch_ivr_audit'
--From Chr_Utils.dbo.syscomments s JOIN Chr_Utils.dbo.sysobjects o ON s.id = o.id 
--WHERE  s.text like '%ch_ivr_audit%'

--, '+['@name']+','+['@tblname']+

 

2. Among jobs

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  -- Instead of use nolock everytime

DECLARE              @Str VARCHAR(100)

DECLARE tbl_cursor CURSOR FOR   
SELECT searchstr
FROM StagingData.dbo.stgETL0Cleanup        stc
WHERE  stc.SearchStr not like 'ch%'

    OPEN tbl_cursor
    FETCH NEXT FROM tbl_cursor INTO @Str
   
        While @@FETCH_STATUS = 0
            BEGIN

                SELECT  @Str = '%' + @Str + '%'

                SELECT                            @str as SearchingName,
                                                CONVERT(VARCHAR(50),jo.name) AS JobName,
                                                jo.enabled,
                                                js.last_run_date,
                                                CONVERT(VARCHAR(30),js.database_name) AS dbname,
                                                CONVERT(VARCHAR(50), js.step_name) AS step_name,
                                                REPLACE(REPLACE(js.command, CHAR(10), ''), CHAR(13), '') AS Cmd,
                                                js.step_id
                FROM    msdb.dbo.sysjobs           jo
                JOIN      msdb.dbo.sysjobsteps js
                ON     jo.job_id     = js.job_id
                WHERE
                    (  jo.name    LIKE @Str
                        OR          js.step_name   LIKE @Str
                        OR          js.command       LIKE @Str
                      )
                                               
                ORDER BY jo.job_id, js.step_id
               
                FETCH NEXT FROM tbl_cursor INTO @Str
       
            END

        Close tbl_cursor
        DEALLOCATE tbl_cursor

SET TRANSACTION ISOLATION LEVEL READ COMMITTED