Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Query by Column Name

Author  Topic 

Starting Member

36 Posts

Posted - 2002-03-01 : 11:48:33
I have one recordset that returns all of the column names from a table. I want to be able to base the next recordset on the value of the fields that reside under that column name for a certain record(CSS#).

The first recordset works fine using INFORMATION_SCHEMA.COLUMNS

"SELECT * FROM ETATEMP WHERE Recordset2.Fields.Item("COLUMN_NAME").Value = ????????? AND CCS = '" & target & "'"

Thanks in advance.

SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-20 : 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)
set nocount on

declare @sSQL nvarchar(1000)

--get column names
create table #b (colname nvarchar(50),collist nvarchar(1000))
insert into #b
from sysColumns inner join sysobjects
on =
where = @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

as they say in the classics - HTH

(PS I stole the idea from graz - )

I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

- Advertisement -