search all table fields in DB

[kleinma]

Is there a way in SQL to search for a value in ALL fields of ALL records...

for example, when you do

Select * from tblTest WHERE [Name] = 'kleinma'

but what if kleinma could be any field.. not just name.. and i wanted to do a search like that...

[szlamany]

You could concatenate all the fields together and use CHARINDEX or LIKE '%KLEINMA%'.

Doesn't sound like the optimizer would like that too much - but then again, you are searching the entire table anyway - so it's going to have to do a table scan one way or the other.

[RhinoBull]

Originally posted by kleinma
Is there a way in SQL to search for a value in ALL fields of ALL records ..

NOPE ... each field individually, tough.

[kleinma]

well I plan on having a search on selection, but they want to also be able to just type in anything that may be associated with the account and search like that. I personally thinks its a pain in the ass and will be bad for resources..

[techgnome]

yes it's possible, and I have such a script.....

let me go find it...

TG

[techgnome]

ah! There it is:

DECLARE @SearchCrit varchar(100)
DECLARE @TableName varchar(100)

SET @SearchCrit = 'TechGnome'
SET @TableName = 'Your Table'

DECLARE @ColName varchar(100)
DECLARE @SQL varchar(2048)

SET @SQL = ''

DECLARE curCols CURSOR FOR
	SELECT sc.[name] AS ColName
	FROM syscolumns sc
	INNER JOIN systypes st ON sc.xtype = st.xusertype
	INNER JOIN sysobjects so ON sc.[id]=so.[id]
	LEFT JOIN sysproperties sp ON sc.[id] = sp.[id] AND sc.colid = sp.smallid
	WHERE so.xtype='U' AND so.[name] = REPLACE(REPLACE(@TableName, '[', ''), ']', '')
	ORDER BY so.[name], sc.colorder


OPEN curCols
FETCH NEXT FROM curCols INTO @ColName
WHILE @@FETCH_STATUS = 0
  BEGIN
	SET @SQL = @SQL + ' OR ' + @ColName + ' LIKE ''%' + @SearchCrit + '%'''
	FETCH NEXT FROM curCols INTO @ColName
  END

CLOSE curCols
DEALLOCATE curCols

SET @SQL = 'SELECT * FROM ' + @TableName + ' WHERE 1=0 ' + @SQL

-- PRINT @SQL

EXEC (@SQL)

TG

[Bruce Fox]

G'Day kleinma,
This was my inital thought (ie looping past each Table).




Bruce.

[kleinma]

Originally posted by techgnome
ah! There it is:

DECLARE @SearchCrit varchar(100)
DECLARE @TableName varchar(100)

SET @SearchCrit = 'TechGnome'
SET @TableName = 'Your Table'

DECLARE @ColName varchar(100)
DECLARE @SQL varchar(2048)

SET @SQL = ''

DECLARE curCols CURSOR FOR
	SELECT sc.[name] AS ColName
	FROM syscolumns sc
	INNER JOIN systypes st ON sc.xtype = st.xusertype
	INNER JOIN sysobjects so ON sc.[id]=so.[id]
	LEFT JOIN sysproperties sp ON sc.[id] = sp.[id] AND sc.colid = sp.smallid
	WHERE so.xtype='U' AND so.[name] = REPLACE(REPLACE(@TableName, '[', ''), ']', '')
	ORDER BY so.[name], sc.colorder


OPEN curCols
FETCH NEXT FROM curCols INTO @ColName
WHILE @@FETCH_STATUS = 0
  BEGIN
	SET @SQL = @SQL + ' OR ' + @ColName + ' LIKE ''%' + @SearchCrit + '%'''
	FETCH NEXT FROM curCols INTO @ColName
  END

CLOSE curCols
DEALLOCATE curCols

SET @SQL = 'SELECT * FROM ' + @TableName + ' WHERE 1=0 ' + @SQL

-- PRINT @SQL

EXEC (@SQL)

TG

TG,
You think it is a DB server resource hog to do it like this???

I also was thinking.... there technically are just a handful of fields to search on... the most common is phone number, serial number, and then name, addr, etc....

so what I was going to do, was validate the text in VB before creating the query string. if its 10 numbers, search on phone number fields, if its a serial number, search on the SN field, otherwise search on the other varchar fields like name, addr etc..

strSQL = "SELECT * FROM tblC_Accts WHERE [B_Phone] = '" & s & "' OR [B_Fax] = '" & s & "' OR [S_Phone] = '" & s & "' OR [S_Fax] = '" & s & "'"

here is an example for when its a phone number search.. is there anyway I can just list multiple fields and 1 search value (since S is the same value for all the fields im searching on)

[Jlarini]

Hi!

Your example is a bad way, but it's a way...

Following your example, you can get all the fields names from the table, and "mount" dinamically a SQL command line...

It's only a idea, based in your example... MSDN has a sample called "Visdata" that shows how to get the fieldnames (and much more)...


BTW, I don't know if the SQL command line will hold all this data, cos it could be too long...

I hope it helps you to find a solution...

JL

[techgnome]

Originally posted by kleinma
TG,
You think it is a DB server resource hog to do it like this???

I also was thinking.... there technically are just a handful of fields to search on... the most common is phone number, serial number, and then name, addr, etc....

so what I was going to do, was validate the text in VB before creating the query string. if its 10 numbers, search on phone number fields, if its a serial number, search on the SN field, otherwise search on the other varchar fields like name, addr etc..

strSQL = "SELECT * FROM tblC_Accts WHERE [B_Phone] = '" & s & "' OR [B_Fax] = '" & s & "' OR [S_Phone] = '" & s & "' OR [S_Fax] = '" & s & "'"

here is an example for when its a phone number search.. is there anyway I can just list multiple fields and 1 search value (since S is the same value for all the fields im searching on)

Well, the times I've used it's been against one of our larger tables (both in cols and rows) and it's quite quick. At least the building of the SQL is fast, execution is dependant on other factors obviously.

The consept behind the script was that it allowed me to search on any table I specified w/o needing to know the table structure. If I want to find TechGome Software in the Accounts table, I set the @SearchCrit = 'TechGnome' and the @TableName = 'Accounts'.... if I then want to find all Invoices for TechGnome, I change the @TableName to 'Invoices' and run it. It was more utilitarian in nature.

But it can be easily customized... you can filter out cols based on type, or even name, if you knwo you will (or will not) search by certain cols.

Originally posted by Jlarini
Hi!

Your example is a bad way, but it's a way...

Following your example, you can get all the fields names from the table, and "mount" dinamically a SQL command line...

It's only a idea, based in your example... MSDN has a sample called "Visdata" that shows how to get the fieldnames (and much more)...


BTW, I don't know if the SQL command line will hold all this data, cos it could be too long...

I hope it helps you to find a solution...

JL

Whose is a bad way, his or mine? Could you explain the "mount dinamically[sic] a SQL command line..." ??? What does that mean? Also does that Visdata example display the field names in VB? or can it do it on the SQL Server and search them? Hey, that's what my code same already does.

BTW: The limit of a single line in SQL is roughly 8000 give or take a few bytes.



Another option, since it looke like you are creating the SQL in VB (ick!) is to have a constant string:

Private Const SQL_SELECT = "SELECT * FROM tblC_Accts WHERE [B_Phone] = '??' OR [B_Fax] = '??' OR [S_Phone] = '??' OR [S_Fax] = '??'"

Then you could do this:

strSQL  = Replace(SQL_SELECT, "??", s)

If you need to add fields, just add the appropriate OR clause to the constant.

Don't know if any of this is helpful or not.

TG

[Jlarini]

Tech,

My previous answer is about the kleinma's Phonebook example.
Your example, I didn't read it completelly is perfect, but as I understand, it is something like a SP for SQLServer, and kleinma can't (or wants) use that...

The visdata sample came on MSDN CD's and the answer is Yes, it gets the fieldsnames in VB. The size I reffered is the String that will hold the command line...

And I guess my suggestion and yours are very similar.

JL

[kleinma]

my access to the SQL Server is limited... I can't make stored procs.. i really just have the table data and can read/write to it

[techgnome]

Originally posted by Jlarini
Tech,

My previous answer is about the kleinma's Phonebook example.
Your example, I didn't read it completelly is perfect, but as I understand, it is something like a SP for SQLServer, and kleinma can't (or wants) use that...

The visdata sample came on MSDN CD's and the answer is Yes, it gets the fieldsnames in VB. The size I reffered is the String that will hold the command line...

And I guess my suggestion and yours are very similar.

JL

Ah, OK... just wondering.

I looked over the posts, and no where do I see where kleinma state he doesn't want to (or can't) use SPs.... If that's the case, then the SQL would need to be dynamically built in VB then executed. So, I guess until kleinma clears the air, we won't know one way or the other.

TG

[kleinma]

lol i answered your question right above

must have been some cross posting goin on