Search all tables, all columns for a specific value SQL Server

Sql Server Problem Overview

I have a specific value, let's say string 'comments'. I need to find all instances of this in the database as I need to do an update on the format to change it to (*) Comments.

How can I do this? The database is in SQL Server 2000 format.

Sql Server Solutions

Solution 1 - Sql Server

I've just updated my blog post to correct the error in the script that you were having Jeff, you can see the updated script here: Search all fields in SQL Server Database

As requested, here's the script in case you want it but I'd recommend reviewing the blog post as I do update it from time to time

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = '## YOUR STRING HERE ##'
 
 
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Updated and tested by Tim Gaunt
-- http://www.thesitedoctor.co.uk
-- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
-- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
-- Date modified: 03rd March 2011 19:00 GMT
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', 'int', 'decimal')
                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
 
DROP TABLE #Results

Solution 2 - Sql Server

I published one here: FullParam SQL Blog

/* Reto Egeter, fullparam.wordpress.com */

DECLARE	@SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
SET @SearchStrColumnValue = '%searchthis%' /* use LIKE syntax */
SET @FullRowResult = 1
SET @FullRowResultRows = 3
SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */
SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */
SET @SearchStrInXML = 0 /* Searching XML data may be slow */

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')
DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))

WHILE @TableName IS NOT NULL
BEGIN
	SET @TableName = 
	(
		SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
		FROM 	INFORMATION_SCHEMA.TABLES
		WHERE 		TABLE_TYPE = 'BASE TABLE'
			AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
			AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
			AND	OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
	)
	IF @TableName IS NOT NULL
	BEGIN
		DECLARE @sql VARCHAR(MAX)
		SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(''' + @TableName + ''', 2)
				AND	TABLE_NAME	= PARSENAME(''' + @TableName + ''', 1)
				AND	DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')
				AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END  + ',COLUMN_NAME)'
		INSERT INTO @ColumnNameTable
		EXEC (@sql)
		WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
		BEGIN
			PRINT @ColumnName
			SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
			SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),''' 
			WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),'''
			ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + ''' 
					FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' 
					WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
					ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
			INSERT INTO #Results
			EXEC(@sql)
			IF @@ROWCOUNT > 0 IF @FullRowResult = 1 
			BEGIN
				SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' +
					' FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' 
					WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
					ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
				EXEC(@sql)
			END
			DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
		END	
	END
END
SET NOCOUNT OFF

SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
GROUP BY TableName, ColumnName, ColumnValue, ColumnType

Solution 3 - Sql Server

You could query the sys.tables database view to get out the names of the tables, and then use this query to build yourself another query to do the update on the back of that. For instance:

select 'select * from '+name from sys.tables

will give you a script that will run a select * against all the tables in the system catalog, you could alter the string in the select clause to do your update, as long as you know the column name is the same on all the tables you wish to update, so your script would look something like:

select 'update '+name+' set comments = ''(*)''+comments where comments like ''%comment to be updated%'' ' from sys.tables

You could also then predicate on the tables query to only include tables that have a name in a certain format, or are in a subset you want to create the update script for.

Solution 4 - Sql Server

The below Query works but very slow... copied from vyaskn.tripod.com

Declare	@SearchStr nvarchar(100)
 
SET  @SearchStr='Search String' 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

Content Type	Original Author	Original Content on Stackoverflow
Question	jhowe	View Question on Stackoverflow
Solution 1 - Sql Server	Tim	View Answer on Stackoverflow
Solution 2 - Sql Server	regeter	View Answer on Stackoverflow
Solution 3 - Sql Server	James B	View Answer on Stackoverflow
Solution 4 - Sql Server	BMG	View Answer on Stackoverflow