How to search for a string in all tables of an SQL database

A guide on how to search for a specific string across all tables and columns in an SQL database using dynamic queries. This is useful for identifying the presence of a string anywhere in the database.

In large databases, finding a specific string across all tables and columns can be complex. This article will guide you through creating a dynamic SQL query to search for a string in the entire database.

SQL Code

Here is an SQL script to search for a string across all tables in the database. This example is for SQL Server:

DECLARE @SearchString NVARCHAR(100)
SET @SearchString = 'your search string'

DECLARE @sql NVARCHAR(MAX)

SET @sql = ''

SELECT @sql = @sql + 'SELECT ''' + TABLE_NAME + ''' AS TableName, ''' + COLUMN_NAME + ''' AS ColumnName, ' 
              + COLUMN_NAME + ' AS ColumnValue FROM ' + TABLE_SCHEMA + '.' + TABLE_NAME 
              + ' WHERE ' + COLUMN_NAME + ' LIKE ''%' + @SearchString + '%'' UNION ALL '
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

SET @sql = LEFT(@sql, LEN(@sql) - 10) -- Remove the last ' UNION ALL '

EXEC sp_executesql @sql

Detailed explanation:

1. DECLARE @SearchString NVARCHAR(100): Declares a variable to store the search string.
2. SET @SearchString = 'your search string': Assigns the search string to the variable.
3. DECLARE @sql NVARCHAR(MAX): Declares a variable to hold the dynamic query.
4. SELECT @sql = @sql + ... FROM INFORMATION_SCHEMA.COLUMNS: Queries all char, varchar, nchar, and nvarchar columns from all tables and builds the dynamic SQL query.
5. SET @sql = LEFT(@sql, LEN(@sql) - 10): Removes the trailing ' UNION ALL ' from the query.
6. EXEC sp_executesql @sql: Executes the dynamic query to search for the string in the database.

System requirements:

• SQL Server
• Database access with SELECT permission

Tips:

• Always back up your database before running queries on large databases to prevent data loss.
• This query only works with string data types. If you have other data types, you may need to extend the query.

---