Gitendra Malla's Blog

1. Use the stored procedure that searches all the columns in all the tables in the db for a given keyword, or
2. Export all the tables to an excel/ text file and use the find/replace feature of the respective applications.

You can use SSIS (or DTS for SQL 200) package to transfer data to an excel file using the data transfer wizard. However, in DTS, data transfer to an excel file does not work if:

- the table contains certain data fields that the individual cells of the excel spreadsheet cannot handle(image file, binary data type). To get around this problem, ignore these columns during the transfer.

- the table name contains non-alphanumeric characters ($, _)

Here are the two stored procedures that can be used to search all tables and replace keywords with your entry:

To search all tables: (thanks to Narayana Vyas Kondreddi)

Note: these stored procedure don't search ntext or text data types coumns. The command to run the stored procedure is as follows:

EXEC SearchAllTables 'search_term' GO

CREATE PROC SearchAllTables
(
 @SearchStr nvarchar(100)
)
AS
BEGIN

 -- 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
 -- Tested on: SQL Server 7.0 and SQL Server 2000
 -- Date modified: 28th July 2002 22:50 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')
     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

To search and replace: (thanks to Narayana Vyas Kondreddi)

Note: these stored procedure don't search ntext or text data types coumns. The command to run the stored procedure is as follows:

EXEC SearchAndReplace 'search_term' 'replace_with'  GO

CREATE PROC SearchAndReplace
(
 @SearchStr nvarchar(100),
 @ReplaceStr nvarchar(100)
)
AS
BEGIN

 -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
 -- Purpose: To search all columns of all tables for a given search string and replace it with another string
 -- Written by: Narayana Vyas Kondreddi
 -- Site: http://vyaskn.tripod.com
 -- Tested on: SQL Server 7.0 and SQL Server 2000
 -- Date modified: 2nd November 2002 13:50 GMT

 SET NOCOUNT ON

 DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
 SET  @TableName = ''
 SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 SET @RCTR = 0

 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
    SET @SQL= 'UPDATE ' + @TableName +
      ' SET ' + @ColumnName
      + ' =  REPLACE(' + @ColumnName + ', '
      + QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
      ') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
    EXEC (@SQL)
    SET @RCTR = @RCTR + @@ROWCOUNT
   END
  END 
 END

 SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END

GO