Search all tables in a database

search-all-tables.sql

DECLARE @SearchStr nvarchar(100) = 'brent'
DECLARE @SearchSchema nvarchar(100) = 'dbo'

CREATE TABLE #Results (
	TableName nvarchar(255),
	ColumnName nvarchar(255),
	ColumnValue nvarchar(MAX)) 
SET NOCOUNT ON
DECLARE @TableName nvarchar(255) = '',
	@ColumnName nvarchar(255),
	@SearchStr2 nvarchar(255) = QUOTENAME('%' + @SearchStr + '%','''')
 
WHILE @TableName IS NOT NULL
BEGIN
	SET @ColumnName = ''
	SET @TableName = 
	(
		SELECT
			MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
		FROM INFORMATION_SCHEMA.TABLES
		WHERE
			TABLE_TYPE = 'BASE TABLE'
			AND
			TABLE_SCHEMA = @SearchSchema
			AND
			QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
			AND
			OBJECTPROPERTY(
				OBJECT_ID(
					QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
				), 'IsMSShipped'
			) = 0
	)
 
	WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)     
	BEGIN
		SET @ColumnName =
		(
			SELECT
				MIN(QUOTENAME(COLUMN_NAME))
			FROM INFORMATION_SCHEMA.COLUMNS
            WHERE
				TABLE_SCHEMA = PARSENAME(@TableName, 2)
				AND
				TABLE_NAME    = PARSENAME(@TableName, 1)
				AND
				DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
				AND
				QUOTENAME(COLUMN_NAME) > @ColumnName
		)
 
		IF @ColumnName IS NOT NULL
		BEGIN
			INSERT INTO #Results
			EXEC
			(
				'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',
				LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
				' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
			)
		END
	END   
END
 
SELECT
	TableName,
	ColumnName,
	ColumnValue,
	COUNT(*)
FROM #Results
GROUP BY TableName, ColumnName, ColumnValue
 
DROP TABLE #Results