Search for a Text/String in Entire Database (SQL Server)

In this blog you will learn search for a text/string in SQL Database using stored procedures.

There are situations where you have to search for a text/string in sql server, to find out from which table, column & row the data persists and coming to UI.

There are only 2 steps to do that,

1. Need to create a stored procedure.
2. Execute the stored procedure with valid input parameter(s).

Usage of Code

1. CREATE PROC SearchAllTables(@SearchStr nvarchar(100)) AS BEGIN
   DECLARE @Results TABLE(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

2. exec SearchAllTables '%search for some text%'

Result will show the Table Name, Column Name, Column Value (your search text).