Search for a String in All Tables in SQL Server

In SQL Server, you can search for a string in all tables using a combination of system views and dynamic SQL. This is useful when you want to find specific data that matches a certain pattern or phrase across multiple tables.

Using System Views and Dynamic SQL

The basic approach is to:

  1. Get a list of all tables in the database using the `sys.tables` system view.
  2. Loop through each table and search for the string using a dynamic SQL query.
  3. Return the results for each table.

Example 1: Search for a String in All Tables

Let's say we want to search for the string "John" in all tables in the database:

DECLARE @search_string NVARCHAR(50) = 'John'; DECLARE @sql NVARCHAR(MAX) = ''; SELECT @sql = @sql + ' SELECT ''' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ''' AS TableName, * FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' WHERE CAST(' + QUOTENAME(c.name) + ' AS NVARCHAR(MAX)) LIKE ''%' + @search_string + '%'' OR CAST(' + QUOTENAME(c.name) + ' AS NVARCHAR(MAX)) LIKE ''%' + LOWER(@search_string) + '%'' OR CAST(' + QUOTENAME(c.name) + ' AS NVARCHAR(MAX)) LIKE ''%' + UPPER(@search_string) + '%'';' FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN sys.columns c ON t.object_id = c.object_id WHERE c.system_type_id IN (167, 175, 231, 239) 
-- only search in string columns 
EXEC sp_executesql @sql;

This script will search for the string "John" in all string columns of all tables in the database, and return the results for each table.

Example 2: Search for a String in All Tables with a Specific Column Name

Let's say we want to search for the string "John" in all tables that have a column named "EmployeeName":

DECLARE @search_string NVARCHAR(50) = 'John'; DECLARE @column_name NVARCHAR(50) = 'EmployeeName'; DECLARE @sql NVARCHAR(MAX) = ''; SELECT @sql = @sql + ' SELECT ''' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ''' AS TableName, * FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' WHERE ' + QUOTENAME(c.name) + ' LIKE ''%' + @search_string + '%'';' FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN sys.columns c ON t.object_id = c.object_id WHERE c.name = @column_name EXEC sp_executesql @sql;

This script will search for the string "John" in all tables that have a column named "EmployeeName", and return the results for each table.

Important Notes

When searching for strings in all tables in SQL Server, keep in mind:

  • This approach can be slow and resource-intensive, especially for large databases.
  • Make sure to adjust the script to fit your specific needs, such as searching for a specific column name or data type.
  • Be careful when executing dynamic SQL, as it can be vulnerable to SQL injection attacks.