June 5, 2003 at 11:38 am
I think i have done this before, but cannot find the code. I simply want to seach all columns for a given value. It may be slow, but thats ok. Maybe it only searched varchar fields... etc. not too picky.
anyone have an idea of how to tackle this?
I want it dynamic across all tables and fields.
June 5, 2003 at 1:56 pm
You can try this. You will obviosuly need to uncomment the exec at the end for it to do anything.
CREATE proc search_tables @table sysname = 'ALL',
@string varchar(8000)
as
declare@sql varchar(8000),
@separator varchar(10),
@current_table sysname
IF@table = 'ALL'
BEGIN
DECLARE loop_tables CURSOR
FOR SELECT name FROM sysobjects WHERE type = 'U'
END
ELSE
BEGIN
IF OBJECTPROPERTY(OBJECT_ID(@table),'IsUserTable') = 1
BEGIN
DECLARE loop_tables CURSOR
FORSELECT name FROM sysobjects
WHERE type = 'U'
AND name = @table
END
ELSE
BEGIN
PRINT 'Invalid or System table'
RETURN
END
END
OPEN loop_tables
FETCH loop_tables into @current_table
WHILE (@@fetch_status) = 0
BEGIN
select @sql = 'select * from '+@table,
@separator = char(13)+char(10)+'WHERE '
select @sql = @sql + @separator + sc.name + ' LIKE '''+@string+'''',
@separator = char(13)+char(10)+'OR '
from syscolumns sc
where id = object_id(@current_table)
and type in (35,39,47)
select @sql
--exec (@sql)
FETCH loop_tables into @current_table
END
CLOSE loop_tables
DEALLOCATE loop_tables
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply