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

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

Delete Duplicates In One SQL Statement

Using a Microsoft T-SQL extension you are able to conduct a self-join on a DELETE statement. The hitch is you cannot use an alias for the first table in the second FROM clause. This works best if there is at least someway to uniquely idenify the table. This will not work with Updateable Views since they do not allow self-joins.

DELETE FROM tblBlah
FROM tblBlah
JOIN tblBlah a
ON tblBlah.ClientName = a.ClientName
AND tblBlah.ClientId > b.ClientId

For a more complete examination see my test code below. Just copy and paste into query anaylzer and give it a run.

SET NOCOUNT ON
-- Create Duplicate temporary table
IF Object_Id('tempdb.dbo.#dups') IS NOT NULL DROP TABLE #dups
CREATE TABLE #dups(
pk int identity(1,1),
Firstname varchar(50),
LastName varchar(50))-- Populate Duplicate table
Insert into #dups (FirstName, LastName)
Values ('Jack','Sparrow')
Insert into #dups (FirstName, LastName)
Values ('Jack','Sparrow')
Insert into #dups (FirstName, LastName)
Values ('Jack','Sparrow')
Insert into #dups (FirstName, LastName)
Values ('Will','Turner')
Insert into #dups (FirstName, LastName)
Values ('Will','Turner')
Insert into #dups (FirstName, LastName)
Values ('Elizabeth','Swann ')
Insert into #dups (FirstName, LastName)
Values ('Elizabeth','Swann ') -- The Table with all duplicates
SELECT * FROM #dups -- Delete duplicates and keep the min primary key record.
-- Reverse the comparison operator to keep the
-- max primary key record.
DELETE FROM #dups
FROM #dups
JOIN #dups b
ON #dups.FirstName = b.FirstName
AND #dups.LastName = b.LastName
AND #dups.pk > b.pk -- The table de-duped
SELECT *FROM #dups