Agent Jobs

Find Value Across All Tables And Columns

This little bit of SQL Logic will allow you to search for a value across all tables and columns. This is serves as an expansion on Vyaskn’s search script (http://vyaskn.tripod.com). This will allow you to not only find the values, but replace, or delete them if needed.

Note: Wild card search is permitted. Example: ‘%find this%’

use [MyDatabase];
set nocount on

declare	@find_this		varchar(255) = '%MyValue%'

if object_id('tempdb..#found') is not null
	drop table #found
if object_id('tempdb..#foundall') is not null
	drop table #foundall

create table #found		(columnname nvarchar(255), columnvalue nvarchar(3000))
create table #foundall	([table_name] nvarchar(255), [column_name] nvarchar(255), [value] nvarchar(4000))
declare @tablename		nvarchar(256)
declare @columnname		nvarchar(128)
declare @find_this2		nvarchar(110)
set	@tablename		= ''
set	@find_this2		= quotename('%' + @find_this + '%','''')

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 #found
exec
('select ''' + @tablename + '.' + @columnname + ''', left(' + @columnname + ', 3630) from ' + @tablename + ' (nolock) ' +' where ' + @columnname + ' like ' + @find_this2) end end end

insert into #foundall
 select 
	[table_name]		= reverse(substring(reverse(columnname),charindex('.',reverse(columnname))+1,len(columnname)))
,	[column_name]	= reverse(left(reverse(columnname), charindex('.', reverse(columnname)) - 1)) 
,	[value]			= columnvalue
from
	#found
order by
	[table_name]
,	[column_name] asc

-- get all found values
select * from #foundall

-- replace all found values
declare @replace_all_values		varchar(max)
set	@replace_all_values		= ''
select	@replace_all_values		= @replace_all_values + 
' update table ' + [table_name] + ' set ' + [column_name] + ' = '' replace(' + [column_name] + ' replace, ''' + [value] + ''', ''MyNewValue'');' + char(10)
from	#foundall


-- delete rows with found values
/*
declare @delete_rows		varchar(max)
set	@delete_rows		= ''
select	@delete_rows		= @delete_rows + 
' delete from ' + [table_name] + ' where ' + [column_name] + ' = ''' + [value] + ''';' + char(10)
from	#foundall

exec	(@delete_rows) --for xml path (''), type
*/
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s