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

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  
    2. DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))  
    3. SET NOCOUNT ON  
    4. DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)  
    5. SET @TableName = ''  
    6. SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%'''  
    7.     '')  
    8. WHILE @TableName IS NOT NULL  
    9. BEGIN  
    10. SET @ColumnName = ''  
    11. SET @TableName =  
    12.     (  
    13.         SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'  
    14.         AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY(  
    15.             OBJECT_ID(  
    16.                 QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)  
    17.             ), 'IsMSShipped'  
    18.         ) = 0  
    19.     )  
    20. WHILE(@TableName IS NOT NULLAND(@ColumnName IS NOT NULL)  
    21. BEGIN  
    22. SET @ColumnName =  
    23.     (  
    24.         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  
    25.     )  
    26. IF @ColumnName IS NOT NULL  
    27. BEGIN  
    28. INSERT INTO @Results  
    29. EXEC  
    30.     (  
    31.         'SELECT '  
    32.         '' + @TableName + '.' + @ColumnName + ''  
    33.         ', LEFT(' + @ColumnName + ', 3630)  
    34.         FROM ' + @TableName + ' (NOLOCK)  
    35.         ' +  
    36.         ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2  
    37.     )  
    38. END  
    39. END  
    40. END  
    41. SELECT ColumnName, ColumnValue FROM @Results  
    42. END  
  1. exec SearchAllTables '%search for some text%'

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

Ebook Download
View all
Learn
View all