This script is extreme useful in some cases. Please be aware, it’s slow, but it’s simple and works.
If running this SQL against a database in SQL Server Management Studio it will search all string based columns (varchar, nvarchar, text, ntext, char, nchar) in all tables in the current database, for the specified string (in this case ‘searchtext’).
Obviously for production databases you need to use this script with care, especially if there are a lot of columns to search. In that situation it might be better to use a lightweight profiler trace, and see how the application is getting hold of the data.
DECLARE @SQL VARCHAR(MAX)
DECLARE @SearchString VARCHAR(100)
SET @SQL=''
-- ------------------------------------------
-- Enter the string to be searched for here :
--
SET @SearchString='searchtext'
-- ------------------------------------------
SELECT @SQL = @SQL + 'SELECT CONVERT(VARCHAR(MAX),COUNT(*)) + '' matches in column ''+'''
+ C.name + '''+'' on table '' + ''' + SC.name + '.' + T.name +
''' [Matches for '''+@SearchString+''':] FROM ' +
QUOTENAME(SC.name) + '.' + QUOTENAME(T.name) + ' WHERE ' + QUOTENAME(C.name) +
' LIKE ''%' + @SearchString +
'%'' HAVING COUNT(*)>0 UNION ALL ' +CHAR(13) + CHAR(10)
FROM sys.columns C
JOIN sys.tables T
ON C.object_id=T.object_id
JOIN sys.schemas SC
ON SC.schema_id=T.schema_id
JOIN sys.types ST
ON C.user_type_id=ST.user_type_id
JOIN sys.types SYST
ON ST.system_type_id=SYST.user_type_id
AND ST.system_type_id=SYST.system_type_id
WHERE SYST.name IN ('varchar','nvarchar','text','ntext','char','nchar')
ORDER BY T.name, C.name
-- Strip off the last UNION ALL
IF LEN(@SQL)>12
SELECT @SQL=LEFT(@SQL,LEN(@SQL)- 12)
EXEC(@SQL)
--PRINT @SQL
Roselle Hollopeter March 28, 2021
Wow that’s so cool lin how do you liked it?