This request comes up around here about twice a month. I will post some code I wrote for this very thing a number of years ago. There is VERY VERY VERY IMPORTANT condition with this code. DO NOT RUN THIS IN PRODUCTION!!!!!!! The logic of what you have to do here means that we have to look in every single column of every single row of every single table. This is horribly slow and incredibly inefficient. There is no good way to do this. I have seen this run for over 24 hours on a database in the past. Run this ONLY ON A DEV/TEST server!!!!

One last thing...did I mention that you should not run this in production???

declare @table_name varchar(2000)

declare @sSQL nvarchar(4000)

declare @result varchar(20)

declare @column_name varchar(2000)

declare @SearchVal varchar(200)

set @SearchVal = '%your search val here%'

declare @ColName varchar (250)

set @ColName = '%use this if you want to limit to a naming convention on the columns to search (i.e. email)%'

declare SearchList cursor for

select distinct so.name,sc.name from syscolumns sc

inner join sysobjects so on sc.id = so.id

where sc.name like @ColName

and so.type = 'U'

open SearchList

fetch next from SearchList into @table_name, @column_name

while(@@fetch_status = 0)

begin

select @sSQL = 'if exists (select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''') select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''''

exec sp_executesql @sSQL

--select @ssql

fetch next from SearchList into @table_name, @column_name

end

close SearchList

deallocate SearchList

Lowell another long time poster around here has a stored proc that does this same kind of thing. His does some things mine doesn't. Depending on your requirements one or the other may be better suited. The same caveat about not running in production applies here too.

CREATE PROCEDURE sp_UGLYSEARCH

/*

--Purpose: to search every string column in a databasefor a specific word

--returns sql statement as a string which idnetifies the matching table

-- or when the optional parameter is used, the sql statement for the specific matching column.

--usage:

-- EXEC sp_UGLYSEARCH 'Provisional'

-- EXEC sp_UGLYSEARCH 'TEST'

-- creates one SQL for each table that actually has a match for the searched value i.e.

-- SELECT * FROM [ACACTSCR] WHERE [DESCRIP] LIKE '%TEST%' OR [TITLE] LIKE '%TEST%'

--optional parameter SEARCHBYCOLUMN

-- EXEC sp_UGLYSEARCH 'TEST',1

-- creates one SQL for each Column that actually has a match for the searched value i.e.

-- SELECT * FROM [dbo].[ACACTSCR] WHERE [DESCRIP] LIKE '%TEST%'

-- SELECT * FROM [dbo].[ACACTSCR] WHERE [TITLE] LIKE '%TEST%'

*/

@SEARCHSTRING VARCHAR(50),

@SEARCHBYCOLUMN INT = 0

AS

BEGIN

SET NOCOUNT ON

DECLARE @SQL VARCHAR(max),

@SCHEMANAME VARCHAR(100),

@TABLENAME VARCHAR(100),

@COLUMNNAME VARCHAR(100),

@COLZ VARCHAR(max)

CREATE TABLE #RESULTS(SCHEMANAME VARCHAR(100), TBLNAME VARCHAR(100),COLNAME VARCHAR(100),SQL VARCHAR(max))

SELECT

SCHEMA_NAME(schema_id) AS SCHEMANAME,

objz.name AS TBLNAME,

colz.name AS COLNAME,

TYPE_NAME(colz.user_type_id) AS DATATYPE

INTO #TEMP

FROM sys.objects objz

INNER JOIN sys.columns colz ON objz.object_id = colz.object_id

WHERE objz.type='U'

AND TYPE_NAME(colz.user_type_id) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')

AND colz.max_length >= LEN(@SEARCHSTRING) --smart: don't search varchar(1) columns for 'TEST' 4xmpl

ORDER BY TBLNAME,COLNAME

IF @SEARCHBYCOLUMN = 0

BEGIN

DECLARE C1 CURSOR FOR

SELECT SCHEMANAME,TBLNAME,COLNAME FROM #TEMP ORDER BY SCHEMANAME,TBLNAME,COLNAME

OPEN C1

FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME

WHILE @@FETCH_STATUS <> -1

BEGIN

SET @COLZ=''

SELECT @COLZ = @COLZ + QUOTENAME(COLNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'' OR ' FROM #TEMP WHERE TBLNAME=@TABLENAME

--@COLZ has a trailing 'OR ' which must be removed

SET @COLZ = SUBSTRING(@COLZ,1,LEN(@COLZ) -3)

--PRINT @COLZ

SET @SQL = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + @COLZ + ') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''-'','' SELECT * FROM ' + QUOTENAME(@TABLENAME) + ' WHERE ' + REPLACE(@COLZ,'''','''''') + ''') ;'

--PRINT @SQL

EXEC (@SQL)

FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME

END

CLOSE C1

DEALLOCATE C1

END

ELSE --@SEARCHBYCOLUMN <> 0

BEGIN

DECLARE C2 CURSOR FOR

SELECT SCHEMANAME,TBLNAME,COLNAME FROM #TEMP ORDER BY SCHEMANAME,TBLNAME,COLNAME

OPEN C2

FETCH NEXT FROM C2 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME

WHILE @@FETCH_STATUS <> -1

BEGIN

SET @SQL = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + QUOTENAME(@COLUMNNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'')

INSERT INTO #RESULTS(SCHEMANAME,TBLNAME,COLNAME,SQL) VALUES(''' + @SCHEMANAME + ''',''' + @TABLENAME + ''',''' + @COLUMNNAME + ''',''

SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + QUOTENAME(@COLUMNNAME) + ' LIKE ''''%' + @SEARCHSTRING + '%'''' '') ;'

PRINT @SQL

EXEC (@SQL)

FETCH NEXT FROM C2 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME

END

CLOSE C2

DEALLOCATE C2

END --@SEARCHBYCOLUMN <> 0

SELECT * FROM #RESULTS ORDER BY TBLNAME,COLNAME

END --PROC

GO

SELECT * FROM #RESULTS ORDER BY TBLNAME,COLNAME

END --PROC

GO

Happy hunting!!!