Search Database for Text Sql Query

This SQL script is a search script that searches for a specified string of text in all columns of all tables in a SQL Server database. The script uses a dynamic SQL approach, where it builds and executes a SELECT statement on the fly, to search the text in each column.

To use this script, you need to replace the value of the @SearchStr variable with the text that you want to search for. The script then uses a while loop to iterate over all tables in the database, and another while loop to iterate over all columns in each table. The script only searches columns that are of data type char, varchar, nchar, or nvarchar.

The results of the search are stored in a temporary table @Results, which is populated using an INSERT INTO statement. The script returns the results of the search by selecting from the @Results table.

It's important to note that the script uses the NOLOCK hint to bypass the read lock on the tables, which means that it may return inconsistent results if the data in the tables is being updated during the search. Also, the script has a limit of 3630 characters for each column value that it returns, so if the text that you're searching for is longer than 3630 characters, it will be truncated.

Overall, this script provides a quick and efficient way to search for a specific text string in all columns of all tables in a SQL Server database.

BEGIN
declare @SearchStr nvarchar(100) = '%Search text%'
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