Performing a search on the whole table.
-
Not sure if this is in the right place but...
I have a table that has 55 fields in it. I want to search the entire table for the presence of a passed parameter. I seem to remember on my programming course that this was possible without the need for 54 "OR" statements but I can't remember how to do it. I know I could do it by looping through all the fields but on the course, the guy did it with only a few lines of code.
Any suggestions?
John.
-
Check Sysobjects and syscolumns and see any ideas are coming or not. if its not then let me know.
--------------------------------
-
I think I have to use full text search which I don't really want to turn on incase I get a performance hit. I just cant rememebr if that is what we did on the course.
-
I've never used full text search. My only experience with it is reading stuff here. But from that your guess may be correct. I ultimately don't know of any way to do it without referencing all of the columns one way or another.
All of the other ideas I can think of involve either merging the values of the 55 columns into a single, artificial column and then running a LIKE on the result or else using a view. For example:
set @SearchVal = '%' + @SearchVal + '%'
set @div = ':|:'
WHERE (Field1 + @div + Field2 + @div + Field3 + @div + CONVERT(varchar, NumField4) + @div + Field5 .....) LIKE @SearchVal
The @div part is just to prevent matching the pattern with part in the end of one column and the rest in the beginning of the next column. For example, if Field1 = 'This' and Field2 = 'That' I'm assuming you wouldn't want to get a match looking for 'sith' or 'ThisThat'.
The above basic idea could be placed into a view. This would ultimately resolve into very short SQL that references a view with a large where clause embedded in it.
An alternative version of the view approach is to use a WHERE clause with all the OR conditions on each of the fields all comparing the same value. Since we now are talking about a parameterized view we aren't talking about a real view anymore but a function instead. The function would take an input parameter of the value to compare and return a variable based table with the results.
A bad option would be to query the schema to build the SQL within code and then EXEC the generated dynamic SQL. Bad option. Don't use it.
-
Searching for a single argument across 55 columns is going to be ugly, no matter how you do it. Most likely, you're wanting to do use a LIKE '%something-to-look-for%' sarg, which can not be optimized with indexes (not to mention having to have 55 indexes even if it was a valid sarg). With that in mind, it seems that if you want performance, Full Text indexing would be best.
--Mitch -
This isn't what you asked for but maybe you can modify it or get ideas from it. It searchs an entire database for a passed string:
CREATE PROC SearchAllTables(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
 
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
 
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
 
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
GO
-
Thanks for the input guys. I am going to try the stored procedure that Tyson posted. That looks as if it may answer my question, I didn't want loads of VB SQL to do this but if I can store the hard stuff in a SP and pass it the parameter, this may be the solution.
Thanks again.
-
2 cents:
I think 'the guy' on the course did it with VB Script, ADO Recordset, foreaching all records, foreaching all fields and comparing the values.
Regards, Hanslindgren!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply