Procedure for Searching Text in Database Tables

Next Post
By Admin at 17 Mar 2010, 16:58 PM
  • I had come across a requirement to search a string in all columns of all tables of a database with several varchar columns each, looking for various string data. I had to find a stored procedure to search text through all tables. It snakes through the schema of your selected database looking for all chars, varchars, nchars, and nvarchars (Text datatype has been eliminated for performance) looking for my string. It returns the name of the table and column, and the containing text block. I ran it on our staging system and it found my string in about 30 seconds... not too shabby.


    The output of this stored procedure contains two columns:

    • The table name and column name in which the search string was found
    • The actual content/value of the column (Only the first 3630 characters are displayed)


    Caution


    Before you go ahead and run this procedure take a look at the following.

    Though this procedure is quite quick on smaller databases, it could take hours to complete on a large database with too many character columns and a huge number of rows. So, if you are trying to run it on a large database, be prepared to wait (I did use the locking hint NOLOCK to reduce any locking). It is efficient to use the Full-Text search feature for free text searching, but it doesn't make sense for this type of ad-hoc requirement.

    How to run it


    Assume that you have to find the text “Wild” in Northwind database

    1. --To search all columns of all tables in Northwind database for the keyword "Wild"
    2. EXEC dbo.prcFindTextInAllTables 'Wild'
    3. GO

    Here is the complete stored procedure code:

    1. CREATE PROC dbo.prcFindTextInAllTables
    2. (
    3. @SearchStr nvarchar(100)
    4. )
    5. AS
    6. BEGIN
    7. -- Purpose: To search all columns of all tables for a given search string
    8. CREATETABLE #TestResults (ColName nvarchar(370), ColumnValue nvarchar(3630))
    9. SET NOCOUNT ON
    10. DECLARE @TblName nvarchar(256), @ColName nvarchar(128), @SearchStr2 nvarchar(110)
    11. SET @TblName = ''
    12. SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''')
    13. WHILE @TblName ISNOTNULL
    14. BEGIN
    15. SET @ColName = ''
    16. SET @TblName =
    17. (
    18. SELECTMIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
    19. FROM INFORMATION_SCHEMA.TABLES
    20. WHERE TABLE_TYPE = 'BASE TABLE'
    21. AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TblName
    22. AND OBJECTPROPERTY(
    23. OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
    24. ), 'IsMSShipped') = 0
    25. )
    26. WHILE (@TblName ISNOTNULL) AND (@ColName ISNOTNULL)
    27. BEGIN
    28. SET @ColName =
    29. (
    30. SELECTMIN(QUOTENAME(COLUMN_NAME))
    31. FROM INFORMATION_SCHEMA.COLUMNS
    32. WHERE TABLE_SCHEMA = PARSENAME(@TblName, 2)
    33. AND TABLE_NAME = PARSENAME(@TblName, 1)
    34. AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
    35. AND QUOTENAME(COLUMN_NAME) > @ColName
    36. )
    37. IF @ColName ISNOTNULL
    38. BEGIN
    39. INSERTINTO #TestResults
    40. EXEC
    41. (
    42. 'SELECT ''' + @TblName + '.' + @ColName + ''', LEFT(' + @ColName + ', 3630)
    43. FROM' + @TblName + ' (NOLOCK) ' +
    44. ' WHERE ' + @ColName + ' LIKE ' + @SearchStr2
    45. )
    46. END
    47. END
    48. END
    49. SELECT ColName, ColumnValue FROM #TestResults
    50. END

    Comments

     

    Post a comment

    Please correct the following: