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
