Link to home
Start Free TrialLog in
Avatar of sandya_116
sandya_116

asked on

Search for a string in all tables.

I need a script to search for a string in all the tables in a database. First I need to list all the tables, columns or rows that have that string. Second I need to replace that string with another string. Thanks for your help.
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of digital_thoughts
digital_thoughts

USE DatabaseName

DECLARE @SearchValue      VARCHAR(100)
SET @SearchValue = 'test'

DECLARE @TableName            VARCHAR(500)
DECLARE @FieldName            VARCHAR(500)
DECLARE @SQL                  NVARCHAR(4000)
DECLARE @ParmDefinition      NVARCHAR(500)
DECLARE @ReturnCnt            INT

-- Build temp table to return table/field value(s)
CREATE TABLE #DataExists
(
      TableName      VARCHAR(500),
      FieldName      VARCHAR(500)
)

-- Get tables and fields of each table
DECLARE myCursor CURSOR FOR (
      SELECT
            O.name AS TableName,  
            c.name AS FieldName
      FROM
            sysobjects O INNER JOIN syscolumns C ON C.id = O.id
      WHERE
            O.xtype = 'U'
)
OPEN myCursor

-- MoveFirst
FETCH NEXT FROM myCursor INTO @TableName, @FieldName
--While not eof loop
WHILE @@FETCH_STATUS = 0
BEGIN

      SET @SQL = N'SELECT @ReturnCnt = COUNT(*) FROM [' + @TableName + '] WHERE (CAST([' + @FieldName + '] AS VARCHAR(500)) = ''' + @SearchValue + ''')'
      SET @ParmDefinition = N'@ReturnCnt INT OUTPUT'
      EXECUTE sp_executesql @SQL, @ParmDefinition, @ReturnCnt OUTPUT      

      IF @ReturnCnt > 0
      BEGIN
            -- Add record to temp table
            INSERT INTO #DataExists (TableName, FieldName) VALUES(@TableName, @FieldName)
      END

      --MoveNext
      FETCH NEXT FROM myCursor INTO @TableName, @FieldName
END
--End Loop

Close myCursor  -- Closed Recordset

DEALLOCATE myCursor -- Clear from memory

SELECT * FROM #DataExists -- Data to return

DROP TABLE #DataExists -- Remove temp table
Hee's code that I use.  It's flexible and won't try to search columns that you don't need.  It also avoids problems with trying to search a numeric field with text.
Declare @TN as varchar(200), @CN as varchar(200), @myValue varchar(30), @SQL as nvarchar(1000)
, @SN as varchar(200), @Exact_Match bit
 
Create Table #myTable (Table_Name varchar(200), Column_Name varchar(200), Number_Of_Rows int)
 
-- Replace @myValue with the value you're searching for in the database
Set @myValue = 'mySearchValue' 
-- 0 for LIKE match, 1 for exact match
Set @Exact_Match = 0    
 
Declare myCursor Cursor For
Select T.Table_Name, C.Column_Name, T.Table_Schema
From INFORMATION_SCHEMA.TABLES T Inner Join INFORMATION_SCHEMA.COLUMNS C 
On T.Table_Schema = C.Table_Schema And T.Table_Name = C.Table_Name
Where T.Table_Name Not In ('dtproperties') And Table_Type = 'Base Table'
And C.Data_Type In ('varchar','char','nvarchar','nchar','sql_variant')
--And C.Data_Type In ('text','ntext')
--And C.Data_Type In ('tinyint','int','bigint','numeric','decimal','money','float','smallint','real','smallmoney')
--And C.Data_Type In ('datetime','dmalldatetime')
-- Fields not searched: image, uniqueidentifier, bit, varbinary, binary, timestamp
Open myCursor
Fetch Next From myCursor Into @TN, @CN, @SN
While @@Fetch_Status <> -1
Begin
	If @Exact_Match = 0
		Set @SQL = N'Insert Into #myTable Select ''' + @SN + '.' + @TN + ''', ''' + @CN + ''', Count(*) From [' + @SN + '].[' + @TN + '] Where [' + @CN + '] Like ''%' + @myValue + '%'''
	    Else
		Set @SQL = N'Insert Into #myTable Select ''' + @SN + '.' + @TN + ''', ''' + @CN + ''', Count(*) From [' + @SN + '].[' + @TN + '] Where [' + @CN + '] = ''' + @myValue + ''''
	--Print @SQL
	Exec sp_executesql @SQL 
	Fetch Next From myCursor Into @TN, @CN, @SN
End
Close myCursor
Deallocate myCursor
Select * From #myTable Where Number_Of_Rows > 0 Order By Table_Name
Drop Table #myTable

Open in new window

Avatar of sandya_116

ASKER

Hi chapmandew, Thanks for the script. I was able to search for a string. Is there a script to replace it with another string. Thanks.
Hi digital_thoughts, Thanks for the script but I got an error: Explicit conversion from data type image to varchar is not allowed.
There is....but I would suggest against doing something like that automatically.  Use the script I gave you to find out the columns, values, etc, and write queries based off of that to do it.  Its something I would be VERY careful with.  Also, it is probably a seperate question....yours was just for searching.

HTH,
Tim
Did you find out what you were looking for in terms of searching for values in columns?