sentient beings

Have you ever found yourself in need of finding a certain string in an entire database? As a freelance BI consultant, this happens to me rather frequently when analysing where and how data is stored and how to retrieve it.

So I came up with this code. It lets you find a string in any column in any table on your database. It's a pretty length search if you have a lot of data and a lot of columns and tables, but it sure beats searching by hand.

Be warned! This may run a pretty long time on large databases.

 
DECLARE @searchString NVARCHAR(MAX)
DECLARE @sqlString NVARCHAR(MAX)
DECLARE @processedString NVARCHAR(MAX)
 
-- Insert your searchstring below
-- Use \ as escape character
-- examples:
--	to search for 'I'm waiting', use 'I\'m waiting'
--	to search for '25%' use '25\%'
 
SELECT
	@searchString = 'naar achter'
 
DECLARE @resultTable	TABLE
(
	table_name	NVARCHAR(255),
	COL_NAME	NVARCHAR(255),
	value_found	NVARCHAR(MAX),
	OBJECT_ID	INT,
	col_id	INT
)
 
SELECT
	@sqlString = '-- start',
	@processedString = ''
 
WHILE
	@sqlString > ''
BEGIN
 
	SELECT
		@sqlString = '',
		@processedString = ''
 
	SELECT
		@processedString = @processedString +
		CASE
			WHEN DATALENGTH(@sqlString) < 7500 -- assume a sql statement will never go beyond 500 characters
			THEN
		'
			select
				''' + QUOTENAME(SCHEMA_NAME(tbl.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(tbl.OBJECT_ID)) + ''' as table_name,
				''' + col.name + ''',
				NULL as value_found,
				' + CONVERT(NVARCHAR,tbl.OBJECT_ID) + ' as object_id,
				' + CONVERT(NVARCHAR,col.column_id) + ' as col_id
		'
			ELSE
				''
		END,
		@sqlString = @sqlString +
		CASE
			WHEN DATALENGTH(@sqlString) < 7500 -- assume a sql statement will never go beyond 500 characters
			THEN
		'
			select
				top 1
				''' + QUOTENAME(SCHEMA_NAME(tbl.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(tbl.OBJECT_ID)) + ''' as table_name,
				''' + col.name + ''',
				' + col.name + ' as value_found,
				' + CONVERT(NVARCHAR,tbl.OBJECT_ID) + ' as object_id,
				' + CONVERT(NVARCHAR,col.column_id) + ' as col_id
			from
				' + QUOTENAME(SCHEMA_NAME(tbl.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(tbl.OBJECT_ID)) + '
			where
				' + col.name + ' like ''%' + @searchString + '%'' escape ''\''
		'
			else
				''
		end
	from
		sys.columns col
			inner join
		sys.tables tbl
			on	col.object_id = tbl.object_id
			and	col.user_type_id in (231, 167, 175, 239)
			 --	nvarchar, varchar, char, nchar
			 --	no text
			left join
		@resultTable rst
			on	col.column_id = rst.col_id
			and	col.object_id = rst.object_id
	where
		rst.table_name is null
 
	print	@sqlString
	print	@processedString	
 
	insert into
		@resultTable
	exec
		(@sqlString)
 
	insert into
		@resultTable
	exec
		(@processedString)
 
	print	'---8<-----------------------------------------------------------------------------------------------'
 
END
 
SELECT
	table_name,
	COL_NAME,
	value_found
FROM
	@resultTable
WHERE
	value_found IS not null
ORDER BY
	1, 2, 3