Wednesday 13 June 2012

Search a string in all tables


TODO:
1. Handle schema, currently assumes all tables to be in dbo schema
2. Check for table existence before execution


declare @searchString varchar(1000), @sql varchar(max)

select @searchString = '**search string **', @sql = ''


Declare @sqlTable table (id int identity Primary Key, selectsql varchar(7000), isComplete bit)

Insert into @sqlTable
select '

Select top 1 ''' + st.name + ''' _sc12456_TableName, * into #temp
from [' + st.name + '] where [' + sc.name + '] = ''' + @searchString + '''
if (@@rowcount > 0)
  select * from #temp
go
', 0
from sys.columns sc inner join sys.tables st
on sc.object_id = st.object_id
inner join sys.types sc_type on sc.user_type_id = sc_type.user_type_id
where sc_type.name in ('nvarchar', 'nchar', 'varchar', 'char') and sc.max_length >= len(@searchString)
order by st.name

set nocount on

Declare @id int
while (1 = 1)
begin
  set @id = null
  select top 1 @id = id, @sql = selectsql from @sqlTable where isComplete = 0
  if (@id is null) break;
  exec (@sql)
  Update @sqlTable set isComplete = 1 where id = @id
End

No comments:

Post a Comment