February 10, 2011 at 10:19 am
hi
Is there a way we can select for a particular word from all the columns in a particular table rather than specifying every column in a where statemet
I ahve a table with 1 coulmns a-z, and i am trying to find the word test in all the columns, as of now i am using
select * from 1 where
a like '%Test%' or b like '%test%' and so on
can i use any other meathod for this select where all column donot have to be specified fo that table
February 10, 2011 at 10:39 am
2 approaches that you might want to try that I can think of off the top of my head.
1. where a + b + c + d + c ... like '%test%' ie concatenate all of the columns that you are searching for together. Very inefficient.
2. Set up full-text indexing on the table for the columns that you are searching and then use Where Contains(*,'test')
Hope that this provides a little help to get you in the right direction. For information on Full-Text indexing http://msdn.microsoft.com/en-us/library/ms142571.aspx
February 10, 2011 at 11:35 am
thanks Rich
concatenation seems to be my only option as full text indexing is not set up on the DB where i am trying to use contains
is there any other way i can accomplish this
February 10, 2011 at 1:51 pm
here's something i put together a long time ago; this requires one tablescan for the EXISTS() of every table that has a char/varchar/nchar/nvarchar column in it's definition...
so if you have any MillionBillionRow tables, you better not run this on production.
if it finds anything that matches, it produces a SQL you can run separately so you can review the results:
Also, i never bothered to update it for SQL 2005 + to consider schema names...it's just assuming everything is dbo.
example results:
SELECT * FROM [CMCONTACT] WHERE [BIRTHDAY] LIKE '%TEST%' OR [BUSADDRESS] LIKE '%TEST%' OR [BUSADDRESS2] LIKE '%TEST%' OR [BUSEMAIL] LIKE '%TEST%' OR [BUSFAX] LIKE '%TEST%' OR [BUSPHONE] LIKE '%TEST%' OR [BUSPHONEEXT] LIKE '%TEST%' OR [BUSZIPCODE] LIKE '%TEST%' OR [CELLPHONE] LIKE '%TEST%' OR [EDITEDBY] LIKE '%TEST%' OR [FIRSTNAME] LIKE '%TEST%' OR [IDNUMBER] LIKE '%TEST%' OR [LASTNAME] LIKE '%TEST%' OR [MIDDLENAME] LIKE '%TEST%' OR [PERADDRESS] LIKE '%TEST%' OR [PERADDRESS2] LIKE '%TEST%' OR [PEREMAIL] LIKE '%TEST%' OR [PERPHONE] LIKE '%TEST%' OR [PERZIPCODE] LIKE '%TEST%' OR [SALUTATION] LIKE '%TEST%' OR [TITLE] LIKE '%TEST%'
the procedure:
DROP PROCEDURE UGLYSEARCH
go
CREATE PROCEDURE UGLYSEARCH
-- EXEC UGLYSEARCH 'TEST'
-- EXEC UGLYSEARCH 'TEST',1
@SEARCHSTRING VARCHAR(50),
@SEARCHBYCOLUMN INT = 0
AS
SET NOCOUNT ON
DECLARE @sql VARCHAR(max),
@TABLENAME VARCHAR(60),
@COLUMNNAME VARCHAR(60),
@COLZ VARCHAR(max)
CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(7800))
SELECT
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE
INTO #FKFINDER
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
AND SYSCOLUMNS.LENGTH >= LEN(@SEARCHSTRING)
ORDER BY TBLNAME,COLNAME
IF @SEARCHBYCOLUMN = 0
BEGIN
DECLARE C1 CURSOR FOR
SELECT DISTINCT TBLNAME FROM #FKFINDER ORDER BY TBLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @TABLENAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @COLZ=''
SELECT @COLZ= @COLZ + QUOTENAME(COLNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'' OR ' FROM #FKFINDER WHERE TBLNAME=@TABLENAME
--@COLZ has a trailing 'OR ' which must be removed
SET @COLZ = SUBSTRING(@COLZ,1,LEN(@COLZ) -3)
--PRINT @COLZ
SET @sql = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@TABLENAME) + ' WHERE ' + @COLZ + ') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''-'','' SELECT * FROM ' + QUOTENAME(@TABLENAME) + ' WHERE ' + REPLACE(@COLZ,'''','''''') + ''') ;'
--PRINT @sql
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @TABLENAME
END
CLOSE C1
DEALLOCATE C1
END --IF
ELSE
BEGIN
DECLARE C1 CURSOR FOR
SELECT TBLNAME,COLNAME FROM #FKFINDER ORDER BY TBLNAME,COLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @COLZ=''
SELECT @COLZ= @COLZ + QUOTENAME(COLNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'' OR ' FROM #FKFINDER WHERE TBLNAME=@TABLENAME
--@COLZ has a trailing 'OR ' which must be removed
SET @COLZ = SUBSTRING(@COLZ,1,LEN(@COLZ) -3)
--SET @sql = 'SELECT ''' + @TABLENAME + ''' AS TABLENAME,''' + @COLUMNNAME + ''' AS COLUMNNAME,* FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'''
SET @sql = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@TABLENAME) + ' WHERE ' + QUOTENAME(@COLUMNNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + QUOTENAME(@TABLENAME) + ''',''' + QUOTENAME(@COLUMNNAME) + ''','' SELECT * FROM ' + QUOTENAME(@TABLENAME) + ' WHERE ' + QUOTENAME(@COLUMNNAME) + ' LIKE ''''' + @SEARCHSTRING + ''''' '') ;'
PRINT @sql
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
END
CLOSE C1
DEALLOCATE C1
END --ELSE
SELECT * FROM #RESULTS
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply