SQL Search Script

In our data warehouse environment we have over 10 terabytes of data (staging and production reportable), comprised of over 5000 tables populated by over 500 SQL Agent jobs. So, when I get a question about a particular table I don't always know the answer from memory. I refer often to my SQL Search Script. This lets me know which tables, views, stored procedures and jobs relate to a particular search string. The only thing missing is SQL embedded in DTS.

DECLARE @SQL varchar (500)

DECLARE @Search varchar (200)

--Enter your search key-word or table name

SET @Search = 'SearchString'

DECLARE @DBName varchar (30)

DECLARE Databases CURSOR FAST_FORWARD FOR

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

)

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

-- Check 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 + '%'