There are times when I am doing some research or refactoring and I need to find all instances of a value in a database. Things like “remove all instances of a person with id 123” or fix all instances of “The Misspelled Cumpany Name”. To do this I use the following SQL tool that searches all columns in all tables of a database for matching instances of the value passed in.  It takes a while but will find all instances you are looking for.

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
BEGIN
 DROP TABLE #Results
END
/**** Customize ***************************************************/
-- The string you are searching for
DECLARE @SearchStr NVARCHAR(100) SET @SearchStr = '3330'  
-- <''>  Use equals for exact matches or like for finding it in a string.
DECLARE @Operator NVARCHAR(100) SET @Operator = 'equals' 
-- <1> If 1 it searches only columns that are of type 'char', 'varchar', 'nchar', 'nvarchar' 
DECLARE @SearchStringsOnly INT SET @SearchStringsOnly = 0 
-- Search only columns that contain this text in their name.  if blank it searches all columns
DECLARE @InColumnName NVARCHAR(100) SET @InColumnName = 'ID' 
/*****************************************************************/

DECLARE @TableName nvarchar(256) SET @TableName = '' 
DECLARE @ColumnName nvarchar(128) SET @ColumnName = ''

CREATE TABLE #Results (ColumnName NVARCHAR(370), ColumnValue NVARCHAR(3630))

SET NOCOUNT ON

SET @SearchStr  = CASE WHEN @Operator = 'equals' 
                       THEN QUOTENAME('' + @SearchStr + '','''') 
                       ELSE QUOTENAME('%' + @SearchStr + '%','''') END 

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
   --AND QUOTENAME(TABLE_NAME) NOT LIKE '%%'
   
 )

 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 ((@SearchStringsOnly = 1 and DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar'))
         or @SearchStringsOnly = 0)
    AND QUOTENAME(COLUMN_NAME) > @ColumnName
    AND COLUMN_NAME like '%' + @InColumnName + '%'
  )

  IF @ColumnName IS NOT NULL
  BEGIN
   INSERT INTO #Results
   EXEC
   (
    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
    FROM ' + @TableName + ' (NOLOCK) ' +
    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr
   )
   PRINT N'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
    FROM ' + @TableName + ' (NOLOCK) ' +
    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr
  END
 END 
END

SELECT ColumnName, ColumnValue FROM #Results

DROP TABLE #Results

We have some pretty large databases in our system and at times I need to find all columns in a database that have the same name.

DECLARE @ColName VARCHAR(200) = '%cert%'

SELECT Obj.Name AS [TableName], Col.name AS [ColumnName]
FROM DBO.SysObjects  Obj
INNER JOIN DBO.SysColumns Col ON Col.Id = Obj.Id
WHERE Col.Name LIKE @ColName

Here are my results for all columns that have “cert” in the name.