Avatar of samprg
samprg
 asked on

Search in all tables

Hi,

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

Thanks
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
samprg

8/22/2022 - Mon
jorgedeoliveiraborges

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

noifr00

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
jorgedeoliveiraborges

Are you familiar with dynamic sql?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
tigin44

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

ASKER CERTIFIED SOLUTION
jorgedeoliveiraborges

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
jorgedeoliveiraborges

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

mimran18

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
samprg

ASKER
Awesome