|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
 Posted - 03/20/2002 : 17:52:49
|
I'm not sure I've quite understood, but I think this might help:
It's an sp which searches ALL NVARCHAR FIELDS of a given table. Sorry I don't have time to generalise it to all field types...
create procedure spSearchAllFields
@sTableName nvarchar(50),
@sSearchPattern nvarchar(50)
as
set nocount on
declare @sSQL nvarchar(1000)
--get column names
create table #b (colname nvarchar(50),collist nvarchar(1000))
insert into #b
select syscolumns.name,NULL
from sysColumns inner join sysobjects
on syscolumns.id = sysobjects.id
where sysobjects.name = @sTableName
and syscolumns.xusertype = 231 --nvarchar only
--build csv column name list where clause
declare @sWhereClause nvarchar(1000)
set @sWhereClause = ''
update #b
set @sWhereClause = collist = @sWhereClause + 'or ' + colname + ' like ''' + @sSearchPattern + ''' '
--strip off leading 'or '
set @sWhereClause = substring(@sWhereClause, 4,len(@sWhereClause)-3)
--collate SQL
set @sSQL = 'select * from ' + @sTableName + ' where ' + @sWhereClause
exec (@sSQL)
drop table #b
go
as they say in the classics - HTH
(PS I stole the idea from graz - http://www.sqlteam.com/item.asp?ItemID=765 )
--
I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
Query by Column Name
