Finding strings in SQL is usually done within a WHERE clause – you’re searching for rows in a table containing your condition which can of course include a string. Sometimes there may be a need to search for a string across all tables within a database though, which is where this post might help out.
The TSQL script below is database-scoped and searches all tables for a string that’s inserted into the ‘valueToFind‘ variable. There’s also a variable in the script to search for a string within column names if there’s ever a random need for that too. Hope this helps!
SQL Script to Find a String in Tables
! This is by no means the best way to do this, but it should work well enough for that one-off task. !
I’ve had the script below saved for years. I don’t know who wrote it and tried to find out via Google. There are different variations of this out there but this seems to work well enough for me on SQL Server2019.
scope to the desired database before running this
I’m searching for my name with wildcards at both ends. The result is as expected within my test database and contains 2 occurrences of my name.
The screenshot above shows a row per column, for each table that has my name in it.
Now I’m going to check the data within those tables for the string I searched for.
That’s me done for this random info post. For more SQL Server Tips from a SQL DBA in the field, have a look at my SQL Server blog posts page.