When working with SQL Server, you might need to find where a specific string exists in a database. This is especially useful when working with large or unfamiliar schemas.
For example, you may want to check where a value like a username, email, or other data is stored without knowing which table or column contains it. This script automates that process by searching all tables and relevant columns in a database for your desired string.
This is particularly helpful when:
– You don’t know the exact table or column where the data is stored.
– You want to avoid manually inspecting each table.
– You need a quick count of how many times a value appears in each column.
The script dynamically searches all character-based columns for a specific value, and provides a way to search to certain column names if needed.
SQL Script to Find a String in Tables
This script has been in my toolbox for years. I couldn’t trace the original author, but it’s simple and works reliably for SQL Server 2019.
The script dynamically generates queries to scan all matching columns, focusing only on text-based data types like char, varchar, and nvarchar.
Inputs: @valueToFind: The string to search for, with optional wildcards (%) to expand the search. @columnName: Filters columns by name (use '%%' to search all columns).
I’m searching for my name with wildcards at both ends in this example.
The result is as expected within my test database, it contains 2 occurrences of my name.
The script shows which tables and columns contain the string you searched for, along with how many times it appears in each. In this example it’s showing a row for each table that has name in it.
Now I’m going to check the data within those tables for the string I searched for.
Instead of manually inspecting each table or using SSMS filters, this script I’m sharing automates the process. It’s good for one-off searches and provides a clear, actionable output. Just run the script in the database where you want to search, set the variables, and review the results. Perfect for those “I don’t know where this data lives” moments, hope it helps!