We help IT Professionals succeed at work.

Search in all tables

samprg
samprg used Ask the Experts™
on
Hi,

I try write code make search in the all tables for string like "David"
Can I do that?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
>> Can I do that?
Yes, you can.

Commented:
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?

Commented:
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

Open in new window

use yourdatabase
go
select top 100 
      'select * 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 like 'char%'
   or t.name like 'varchar'
order by t2.name asc, c.name asc;

Open 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;

Open in new window

Commented:
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

Author

Commented:
Awesome