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

Chew, Chew, Breath

There is a nasty head cold running though our family. I was stuck with it this past week and ended up taking two miserable days off of work. I optimistically stated in an e-mail to my boss that I would check e-mail occasionally. The problem with this was two-fold. One, my pounding, stuffy, dizzy head mixed with a good dose of mind-altering medication would have prevented me from making sense of anything I read or wrote. Two, had I been clear headed enough, the constant dripping from my over-stuffed head would probably short-circuit the laptop.

It was in this circumstance it stuck me as funny that I was having difficulty with dinner. I had been looking forward to dinner in a "comfort food" kind-of-way. As I sat down and started eating I noticed that I couldn't really taste the great meal my wife had cooked*. On top of that, I was surprised how often I had to stop chewing and breath. It was literally "Chew, chew and breath." What do you do when you can't eat and breath at the same time? You end up chewing faster then you're used to and trying to swallow before you are ready. It is a wonder I didn't choke on a large piece of chicken. So much for "comfort-food."

*Legal disclaimer: Taste and smell are linked therefore my perception of the savoriness of the meal was a result of my illness and not the cook’s ability.