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

Dimensional Modeling

Why is it that the people you most want at a meeting are the ones who inevitably not show up? Such was the case when after 2 years of trying we finally received company financing to get the Kimball group to do on-site dimensional modeling training. The training was great, the benefits of adoption obvious and new energy was pumped into the staff to do things "the best way." The problem? Only one out of the five managers and directors who over-see the staff attended and never really catch the vision of adoption.

So, to keep the vision alive, I created the following power-point (Dimensional Modeling Powerpoint). I've used it a few times over the past year with BI staff and managers who missed the training and with new-hires. Maybe someone looking for a refresher course may find it useful as well.