Find SQL Objects

I am constantly playing detective with our Data Warehouse. We have so many reports using so many tables that without a good data dictionary we have trouble knowing what processes touch what destination tables. The following SQL helps me find most of the Tables, Stored Procedures, Views and SQL Agent Jobs based upon my Search parameter. The exception to this is SQL tied up within a DTS package.

-- Search Criteria
DECLARE @Search varchar (200)
SET @Search = 'seach_keyword'

-- Variables
DECLARE @SQL varchar (500)
DECLARE @DBName varchar (30)
DECLARE Databases CURSOR FAST_FORWARD FOR
SELECT name
FROM master.dbo.sysdatabases

-- Temp table for clean presentation
IF OBJECT_ID('tempdb.dbo.#FindObjects') IS NOT NULL DROP TABLE #FindObjects
CREATE TABLE #FindObjects
(
Database_Name varchar(128) Null,
Obj_Name varchar(128) Null,
dbType char(1) Null
)

-- Find SQL Objects
OPEN Databases
FETCH Databases INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'INSERT INTO #FindObjects '
SET @SQL = @SQL + 'SELECT DISTINCT ''' + @DBName + ''', [Name], a.Type '
SET @SQL = @SQL + 'FROM ' + @DBName + '.dbo.sysobjects a '
SET @SQL = @SQL + 'LEFT JOIN ' + @DBName + '.dbo.syscomments b ON a.ID = b.ID '
SET @SQL = @SQL + 'WHERE a.Type IN (''P'',''U'',''V'')'
SET @SQL = @SQL + 'AND (b.Text LIKE ' + '''' + '%' + @Search + '%' + ''''
SET @SQL = @SQL + 'OR a.Name LIKE ' + '''' + '%' + @Search + '%' + ''')'
EXEC(@SQL)
FETCH Databases INTO @DBName
END
CLOSE Databases
DEALLOCATE Databases

-- Objects Results
SELECT * FROM #FindObjects order by Database_Name, dbType, Obj_Name

-- Find SQL Agent Jobs
SELECT Job_Name = b.name, a.step_id, a.Step_Name, a.subSystem, a.command, a.database_name, a.output_file_name
FROM msdb.dbo.sysJobsteps a
Join msdb.dbo.sysjobs b on a.Job_id = b.Job_id
Where Step_name like '%' + @Search + '%'
or a.command like '%' + @Search + '%'
ORDER BY b.name, a.step_id