?
Solved

How do I search an entire SQL database for a single value?  Say a name like "John Adams"

Posted on 2006-05-17
1
Medium Priority
?
730 Views
Last Modified: 2012-06-27
What syntax is needed to search the fields of all tables in a sql database to find a specific text like a name?  Say "John Adams?

THNX
0
Comment
Question by:frankrentef
1 Comment
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 16700572
declare @phrase varchar(8000)
declare @OutFullRecords bit
declare @sql varchar(8000)
declare @tbl varchar(128)
declare @col varchar(128)
declare @id_present bit


declare @is_char_phrase bit
declare @min_len int
declare @loop_idx int
declare @loop_chr char(1)


set nocount on
select @OutFullRecords = 0
select @phrase = '%John Adams%'


select @loop_idx=1, @is_char_phrase=0, @min_len=0


while @loop_idx<=LEN(@phrase)
begin
set @loop_chr=SUBSTRING(@phrase,@loop_idx,1)
if @loop_chr not in ('%','_') set @min_len=@min_len+1
if @is_char_phrase=0 and @loop_chr not in
('%','_','0','1','2','3','4','5','6','7','8','9','.')
set @is_char_phrase=1
set @loop_idx=@loop_idx+1
end

-- drop table #tbl_res
create table #tbl_res
(TableName varchar(128) not NULL,
ColumnName varchar(128) not NULL,
Id int NULL,
ColumnValue varchar(7500) not NULL)


declare CRR cursor local fast_forward for
select t.name, c.name, 1
from sysobjects t, syscolumns c
where t.type='U'
and c.id=t.id
and c.status&0x80=0 -- Not IDENTITY
and exists (select * from syscolumns c2 where t.id=c2.id and
c2.status&0x80=0x80 and c2.xtype in (48,52,56))
and ( (@is_char_phrase=1 and c.xtype in (175,239,99,231,35,167) and
c.length>=@min_len) -- char only
or (@is_char_phrase=0 and c.xtype not in
(34,165,173,189,61,58,36))) -- char and numeric
union select t.name, c.name, 0
from sysobjects t, syscolumns c
where t.type='U'
and c.id=t.id
and not exists (select * from syscolumns c2 where t.id=c2.id and
c2.status&0x80=0x80 and c2.xtype in (48,52,56))
and ( (@is_char_phrase=1 and c.xtype in (175,239,99,231,35,167) and
c.length>=@min_len) -- char only
or (@is_char_phrase=0 and c.xtype not in
(34,165,173,189,61,58,36))) -- char and numeric
order by 1,2


open CRR
fetch CRR into @tbl, @col, @id_present
while @@FETCH_STATUS=0
begin
if @OutFullRecords=0
begin
set @sql='insert into #tbl_res (TableName,ColumnName,Id,ColumnValue) '
+'select '+char(39)+@tbl+char(39)+', '
+'['+@col+']'+', '
if @id_present=1 set @sql=@sql+'IDENTITYCOL, '
else set @sql=@sql+'NULL, '
set @sql=@sql+'convert(varchar(7500),['+@col+']) '
+'from ['+@tbl+'] (nolock) '
+'where convert(varchar(8000),['+@col+']) like
'+char(39)+@phrase+char(39)
-- select @sql
end
if @OutFullRecords=1
begin
set @sql='if exists (select * from '+@tbl+' (nolock) '
+'where convert(varchar(8000),['+@col+']) like
'+char(39)+@phrase+char(39)+') '
+'select '+char(39)+@tbl+char(39)+' TableName,
'+char(39)+@col+char(39)+' ColumnName, * '
+'from '+@tbl+' (nolock) where convert(varchar(8000),'+@col+')
like '+char(39)+@phrase+char(39)
end
exec(@sql)
fetch CRR into @tbl, @col, @id_present
end
close CRR
deallocate CRR


if @OutFullRecords=0
begin
-- For the clients supporting new types:
--exec('select * from #tbl_res order by 1,2,3')


-- For the clients who are not supporting new types:
exec('select TableName, ColumnName, Id, convert(varchar(255),ColumnValue)
ColumnValue from #tbl_res order by 1,2,3')
end
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question