Tuesday 9 January 2007

SQL : Data Search Script - Version 2

sql data search : version 2
searches an entire database for a search string

1) displays progress of search in the messages window,
2) takes search term as a parameter
3) provides sql at the end to examine the located data

/*
version 2 :    1) displays progress of search in the messages window,
               2) takes search term as a parameter
               3) provides sql at the end to examine the located data
*/
declare @columncount int
declare @searchdata varchar(255)
-- set string to search for here
set @searchdata = 'Utility%Connections'

select @columncount = count(*) from information_schema.columns
 where data_type in ('ntext','smallint','int','char', 'varchar', 'nvchar', 'nvarchar')
 and table_schema = 'dbo'
 and table_name not like 'sync%'
 and column_name <> 'order' 
select 'declare @resultslist varchar(max)' + char(10) + 'set @resultslist = '''''
union
select 'if exists (select 1 from [' + 
  table_name + 
  '] where [' + 
  column_name  + '] like ''%' + @searchdata + '%'' ) ' + char(10) +
  ' begin ' + char(10) +
  '  set @resultslist = @resultslist + char(10) + ''select [' + column_name + '],* from ['+ table_name + '] where [' + column_name + '] like ''''%' + @searchdata + '%'''''''+ char(10) +
  '  print ''' + table_name  + '/' +column_name  + '''' + char(10) + 
  ' end ' + char(10) + 
  '' + char(10) +
  ' print ''' + cast(cast(cast(rank() OVER (ORDER BY table_name, column_name) as float)/ @columncount *100 as decimal(10,2)) as varchar(10))+ '%' +
  '''' + char(10)
 from  information_schema.columns
 where data_type in ('ntext','smallint','int','char', 'varchar', 'nvchar', 'nvarchar')
 and table_schema = 'dbo'
 and table_name not like 'sync%'
 and column_name <> 'order' 
union
select 'print @resultslist' + char(10) + 'go'

No comments: