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 script below is database-scoped and searches all tables for the 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.
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:
DECLARE @SQL VARCHAR(MAX) DECLARE @valueToFind VARCHAR(100) DECLARE @columnName VARCHAR(100) SET @valueToFind = '%pete%' SET @columnName = '%%' CREATE TABLE #TMP (Clmn VARCHAR(500), CNT INT) SELECT @SQL= COALESCE(@SQL,'')+CAST('INSERT INTO #TMP Select ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME + ''' AS Clmn, count(*) CNT FROM ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] WHERE [' + COLUMN_NAME + '] LIKE ''%' + @valueToFind + '%'' ;' AS VARCHAR(MAX)) FROM INFORMATION_SCHEMA.COLUMNS JOIN sysobjects B ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = B.NAME WHERE COLUMN_NAME LIKE @columnName AND xtype = 'U' AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar') EXEC(@SQL) SELECT * FROM #TMP WHERE CNT > 0 DROP TABLE #TMP
I’m searching for my name ‘pete’ with wildcards at both ends. The result is as expected within my test database and has 2 occurrences of my name.
The above shows us a row per column for each table that has my name in it.
Now I’ll just check the data within those tables for the data rows:
And that’s all there is to this one!