Searching For a Specific String in Entire Database

Sometimes we need to search for a piece of string in entire SQL Server database.
Scenario:
Example string: "Currency". You want to look up all database and find the columns that include values like "Currency" or exactly the value "Currency".
It is an enormous effort to write a LIKE query for each table and each column.
The SQL script below helps you determine all columns at once.

Parameter Explanations:

@SEARCH_CRITERIA: The string to be looked up. Use '%' for pattern matching.

@COLUMN_LENGTH_GREATER_THAN: The column length who will be excluded from search operation. If your search criteria already contains 10 characters, it does not make sense query the columns whose max length is less than 10 characters. This parameter is optional.

@TABLE_ROW_COUNT_LESS_THAN: The number of records for the tables who will be excluded from search operation. Possibly, you would not prefer to look large tables when you are looking for a parameter or code definition table. This parameter is optional.

------ Set Parameter Values ------

DECLARE @SEARCH_CRITERIA VARCHAR(100) = '%Currency%'

DECLARE @COLUMN_LENGTH_GREATER_THAN INT = 15

DECLARE @TABLE_ROW_COUNT_LESS_THAN INT = 10000

------ End Of Set Parameter Values ------

DECLARE @TABLE_SCHEMA VARCHAR(100)

DECLARE @TABLE_NAME VARCHAR(100)

DECLARE @COLUMN_NAME VARCHAR(100)

DECLARE @OBJECT_ID INT

DECLARE CUR CURSOR FOR

SELECT

     SCHEMA_NAME(obj.schema_id) as TABLE_SCHEMA_NAME,

     obj.name AS TABLE_NAME,

     col.name AS COLUMN_NAME,

     obj.object_id AS TABLE_OBJECT_ID

FROM

     sys.objects obj (nolock)

     join sys.columns col (nolock)

     on obj.object_id = col.object_id

where

     obj.type = 'U' AND -- Tablo

     col.system_type_id in (167, 175, 231, 239) AND -- varchar, char, nvarchar, nchar

     (ISNULL(@COLUMN_LENGTH_GREATER_THAN, 0) = 0 OR col.max_length = -1 OR col.max_length >= @COLUMN_LENGTH_GREATER_THAN)

ORDER BY 1, 2, 3

    

OPEN CUR

FETCH NEXT FROM CUR INTO @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @OBJECT_ID

DECLARE @RESULT TABLE

(

     TABLE_SCHEMA VARCHAR(100),

     TABLE_NAME VARCHAR(100),

     COLUMN_NAME VARCHAR(100),

     OBJECTID INT

)

DECLARE @ROW_COUNT BIGINT = 0

DECLARE @QUERY NVARCHAR(1000)

DECLARE @IS_QUERYING BIT = 1

WHILE @@FETCH_STATUS = 0

BEGIN

     SET @IS_QUERYING = 1

     IF ISNULL(@TABLE_ROW_COUNT_LESS_THAN, 0) > 0

     BEGIN

         -- Check Table Row Count Criteria

         SET @ROW_COUNT = 0

         SELECT

              @ROW_COUNT = SUM(row_count)

         FROM 

              sys.dm_db_partition_stats STAT (NOLOCK)

         WHERE

              STAT.object_id = @OBJECT_ID AND

              STAT.index_id < 2

         IF @ROW_COUNT > @TABLE_ROW_COUNT_LESS_THAN

              SET @IS_QUERYING = 0

     END

     IF @IS_QUERYING = 1

     BEGIN

         DECLARE @retvalOUT bit = 0

         SET @QUERY = N'SET @retvalOUT = 0 IF EXISTS (SELECT TOP(1) * FROM ' + '[' + @TABLE_SCHEMA + ']' + '.' + '[' + @TABLE_NAME  + '] WITH(NOLOCK)' + ' WHERE [' + @COLUMN_NAME + '] LIKE '''+ @SEARCH_CRITERIA + ''') SET @retvalOUT = 1'

        

         EXEC sp_executesql @QUERY, N'@retvalOUT bit OUTPUT', @retvalOUT=@retvalOUT OUTPUT;

         IF @retvalOUT = 1

         BEGIN

              INSERT INTO @RESULT SELECT @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @OBJECT_ID

         END

     END

     FETCH NEXT FROM CUR INTO @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @OBJECT_ID

END

CLOSE CUR

DEALLOCATE CUR

SELECT * FROM @RESULT