Search All Tables – script

--dba_SearchAllTables.sql
--Searches all columns of all tables for a given search string (4.47 mins)
--EG: exec dba_searchalltables 'patriqu%'

alter proc dba_SearchAllTables
(
	@SearchStr nvarchar(100)
)
AS
begin

	create table #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

	set nocount on

	declare @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
	set  @TableName = ''
--	set @SearchStr2 = quotename('%' + @SearchStr + '%','''')
	SET @SearchStr2 = 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	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')
					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 ColumnName, ColumnValue from #Results
end

GO