Search in all tables

>> Can I do that?
Yes, you can.

Here is a code that I use :


SET NOCOUNT ON
DECLARE @Missing varchar(100)
SET @Missing = 'David'
--Searches for 'David'

SELECT Haystack = TABLE_NAME
INTO Pitchfork
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

SELECT Haystack, COLUMN_NAME AS Needle
INTO Iowa
FROM Pitchfork JOIN INFORMATION_SCHEMA.COLUMNS
ON TABLE_NAME = Haystack
AND DATA_TYPE LIKE '%char'

CREATE TABLE PigsEar (SilkPurse varchar(1000))

DECLARE @FarmQuery varchar(1000)
SET @FarmQuery =
  'INSERT INTO PigsEar SELECT TOP 1 SilkPurse=''Found ' +
REPLACE(@Missing,'''','''''') + ' in HAYSTACK.NEEDLE'' FROM HAYSTACK WHERE ' +
'NEEDLE = ' + QUOTENAME(@Missing,'''')
--if you need find the string as a substring of a column value, change
--the above line to
--'NEEDLE LIKE ' + QUOTENAME('%'+@Missing+'%','''')
--Leave it as is if you want to find the string as an exact match of a
--column value.

DECLARE @Murderer varchar(1000)

DECLARE @Haystack sysname
DECLARE @Needle sysname

DECLARE Investigation CURSOR FOR
SELECT Needle, Haystack FROM Iowa
WHERE Haystack <> 'Pitchfork'

OPEN Investigation

DECLARE @Miracle int

FETCH NEXT FROM Investigation INTO @Needle,@Haystack
WHILE @@FETCH_STATUS = 0 BEGIN
  SET @Murderer =
REPLACE(REPLACE(@FarmQuery,'NEEDLE',quotename(@Needle)),
                'HAYSTACK',quotename(@Haystack))
  exec (@Murderer)
  SELECT @Miracle = COUNT(SilkPurse) FROM PigsEar
  FETCH NEXT FROM Investigation INTO @Needle,@Haystack
END

IF @Miracle IS NULL
  PRINT '['+@Missing+'] not found'
ELSE
  SELECT SilkPurse FROM PigsEar

DEALLOCATE Investigation
go

DROP TABLE Pitchfork
DROP TABLE Iowa
DROP TABLE PigsEar

Are you familiar with dynamic sql?

try somethinh like this... this will list the table and column name along with the tables selected columns...

DECLARE @table	varchar(100);
DECLARE @column	varchar(100);
DECLARE @sql	varchar(1000);


DECLARE ctablecolumns CURSOR FOR
	SELECT O.name, C.name 
	FROM sys.sysobjects O
		INNER JOIN sys.syscolumns C ON O.id = C.id
	WHERE O.xtype = 'U'
	  AND C.type IN (39)  -- modify here to list all the string types...

OPEN ctablecolumns
FETCH NEXT FROM ctablecolumns INTO @table, @column
WHILE (@@FETCH_STATUS = 0)
BEGIN
	SET @sql = 'SELECT '+ @table + ' AS TableName, '+ @column + ' AS ColumnName, * ' +
				' FROM ' + @table + 
				' WHERE '+ @column + ' LIKE ''%David%'''
	EXEC (@sql);
	FETCH NEXT FROM ctablecolumns INTO @table, @column
END
CLOSE ctablecolumns
DEALLOCATE ctablecolumns

Select allOpen in new window

-- use youdatabase
-- go
select top 1000 
      'select ' + c.name + ' from  ' + t2.name + ' where ' + c.name + ' like ' + char (39)+ '%david%' + char (39) + ' ;'
from sys.all_columns c
     inner join sys.types t on (t.system_type_id =c.system_type_id)
     inner join sys.tables t2 on (t2.object_id = c.object_id )
where t.name in ('char', 'varchar', 'xml')
order by t2.name asc, c.name asc;

Select allOpen in new window

CREATE PROC SearchAllTables
(
      @SearchStr nvarchar(100)
)
AS
BEGIN
      CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
 
      SET NOCOUNT ON
 
      DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
      SET  @TableName = ''
      SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 
      WHILE @TableName IS NOT NULL
      BEGIN
            SET @ColumnName = ''
            SET @TableName =
            (
                  SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
                  FROM       INFORMATION_SCHEMA.TABLES
                  WHERE             TABLE_TYPE = 'BASE TABLE'
                        AND      QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                        AND      OBJECTPROPERTY(
                                    OBJECT_ID(
                                          QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                                           ), 'IsMSShipped'
                                           ) = 0
            )
 
            WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
            BEGIN
                  SET @ColumnName =
                  (
                        SELECT MIN(QUOTENAME(COLUMN_NAME))
                        FROM       INFORMATION_SCHEMA.COLUMNS
                        WHERE             TABLE_SCHEMA      = PARSENAME(@TableName, 2)
                              AND      TABLE_NAME      = PARSENAME(@TableName, 1)
                              AND      DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                              AND      QUOTENAME(COLUMN_NAME) > @ColumnName
                  )
 
                  IF @ColumnName IS NOT NULL
                  BEGIN
                        INSERT INTO #Results
                        EXEC
                        (
                              'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                              FROM ' + @TableName + ' (NOLOCK) ' +
                              ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                        )
                  END
            END      
      END
 
      SELECT ColumnName, ColumnValue FROM #Results
END

Go

Awesome