How to search for a String in all Columns in all tables in SQL Server Database - SQL Server / TSQL Tutorial

Scenario: Download Script

You are working as SQL Server developer / TSQL Developer for finical firm. You are working on analysis data from one of the database. You need to find String let say "Aamir" in all the columns of all the table in SQL server database.

Solution:

This analysis can really help us to find if we are strong a lot of duplicate data and what type of index will work better for us. We often get this type of request from business where they like to check the data in all the tables and further dig into it if found.

We are going to use Cursor to find all the string in all the columns in all the tables in SQL Server Database. As this query is going to run for all the columns of datatype char, nchar, varchar, nvarchar,text and ntext. This can be expensive. If your database is small , you might be good to run.
In cases where your database is big in size, you might want to take the backup and restore on server where users are not connected and run your query there. Or run during those hours when you have less or almost no activity.

USE YourDBName
GO

Declare @SearchString VARCHAR(100)
--Provide the String here. I am using Aamir for search
SET @SearchString='aamir'
DECLARE @DatabaseName VARCHAR(100)
DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)
DECLARE @FullyQualifiedTableName VARCHAR(500)
Declare @DataType VARCHAR(50)

--Create Temp Table to Save Results
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results (
    DatabaseName VARCHAR(100)
    ,SchemaName VARCHAR(100)
    ,TableName VARCHAR(100)
    ,ColumnName VARCHAR(100)
    ,ColumnDataType VARCHAR(50)
    ,TotalTableRowCount INT
    ,StringOccuranceRecordCount INT
    )

DECLARE Cur CURSOR
FOR
SELECT C.Table_CataLog
    ,C.Table_Schema
    ,C.Table_Name
    ,C.Column_Name
    ,'[' + C.Table_CataLog + ']' + '.[' + C.Table_Schema + '].' 
    + '[' + C.Table_Name + ']' AS FullQualifiedTableName,
    C.Data_Type
FROM information_schema.Columns C
INNER JOIN information_Schema.Tables T ON C.Table_Name = T.Table_Name
    AND T.Table_Type = 'BASE TABLE'
    and (C.Data_Type like '%CHAR%'
    or C.Data_Type like '%Text%')


OPEN Cur
FETCH NEXT
FROM Cur
INTO @DatabaseName
    ,@SchemaName
    ,@TableName
    ,@ColumnName
    ,@FullyQualifiedTableName
    ,@DataType

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL VARCHAR(MAX) = NULL
    SET @SQL = ' Select ''' + @DatabaseName + ''' AS DatabaseName, ''' 
    + @SchemaName + ''' AS TableName,
      ''' + @TableName + ''' AS SchemaName,
      ''' + @ColumnName + ''' AS ColumnName,
      ''' + @DataType + ''' AS ColumnName,
      (Select count(*) from ' + @FullyQualifiedTableName + ' With (Nolock)) 
      AS TotalTableRowCount,
      count(*) as StringOccuranceRecordCount from ' + @FullyQualifiedTableName 
      + 'With (Nolock) Where  '+@ColumnName+' like '''+'%'+ @SearchString+'%'''

   -- Print @SQL
    INSERT INTO #Results
    EXEC (@SQL)

    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
        ,@SchemaName
        ,@TableName
        ,@ColumnName
        ,@FullyQualifiedTableName
        ,@DataType
END
CLOSE Cur
DEALLOCATE Cur

SELECT *,
Cast((StringOccuranceRecordCount/Cast(TotalTableRowCount as Numeric(13,1)))*100 

AS Numeric(4,1)) AS StringOccurancePercentPerColumn
from #Results
Where StringOccuranceRecordCount<>0
--drop table #Results

Here are my test results

How to search Text in all the columns in all the tables in SQL Server Database - SQL Server Tutorial

Check out our other posts on Data Analysis / Data Validation / Data Cleansing 

• How to Search in all Columns for all tables in a database for Date Value in SQL Server
• How to Find Percentage of Null Values in every Column of all the Tables in SQL Server Database 
• How to Find Percentage of Empty or Blank Values in every Column of all the Tables in SQL Server Database
• How to Get Row Count Of All The Tables In SQL Server Database
• How to find and Replace Text in all the columns in all the tables in SQL server Database
• How to find values with Leading or Trailing Spaces in SQL Server Table 
• How to Trim Leading and Trailing spaces for all the values in all the Columns in all the Tables in a SQL Server Database
• How to Find Duplicate Records in All the Tables in SQL Server Database
• How to Find & Delete Duplicate Records on All Tables in SQL Server Database
• How to Generate Select all Columns with or Without Top X Rows From All Tables Query Dynamically in SQL Server
• Concatenate SQL Server Columns for each Table Query Dynamically in SQL Server 
• How to List all Missing Identity Values for all Tables in SQL Server Database
• How to Find Max and Min Values for all the columns for all the tables in SQL Server Database