Apply like over all columns without specifying all column names?

Question

I've found myself in a position where I'm working an unfamiliar database that has a vast number of columns to each table. I have an idea of what data I'm looking for but I don't know what column it resides in and need to use like in order to locate the exact data that I need (and have to repeat this task for multiple sets of data).

Is there a way to apply like over a cartesian select?

The following should explain what I'd like to do a bit better (even though it's syntactically ridiculous):

select      * from         a_table where        * like '%x%' 

edit:

Note that I'm not intending on using a cartesion select in any reports - it's purposes here would be to help me to identify the relevant columns that I would need to put into my queries and to help me gain familiarity with the database.

Answer 1

Generally - its not possible in reasonable way (without digging in DB metadata), but if you know the names of columns, you may use trick like this:

select      YourTable.* FROM YourTable JOIN (      select        id,        ISNULL(column1,'')+ISNULL(Column2,'')+...+ISNULL(ColumnN,'') concatenated       FROM YourTable ) T ON T.Id = YourTable.Id where   t.concatenated like '%x%' 

OR

if you search for words - use the FTS capabilities, because the upper query is a performance killer

Answer 2

There is a similar discussion here.

There is no direct way and you have to do it in this fashion:

SELECT Name, Age, Description, Field1, Field2 FROM MyTable WHERE Name LIKE 'Something%' OR Description LIKE 'Something%' OR Field1 LIKE 'Something%' OR Field2 LIKE 'Something%' 

One of the solutions posted in that forum was this, This uses dynamic SQL:

CREATE PROCEDURE TABLEVIEWSEARCH @TABLENAME        VARCHAR(60),@SEARCHSTRING VARCHAR(50) -- EXEC TABLEVIEWSEARCH 'GMACT','demo' -- EXEC TABLEVIEWSEARCH 'TABLEORVIEW','TEST' AS SET NOCOUNT ON DECLARE @SQL      VARCHAR(500), @COLUMNNAME       VARCHAR(60)  CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(600)) SELECT    SYSOBJECTS.NAME AS TBLNAME,   SYSCOLUMNS.NAME AS COLNAME,   TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE   INTO #TMPCOLLECTION     FROM SYSOBJECTS       INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID     WHERE SYSOBJECTS.NAME = @TABLENAME     AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')     ORDER BY TBLNAME,COLNAME  DECLARE C1 CURSOR FOR  SELECT COLNAME FROM #TMPCOLLECTION ORDER BY COLNAME OPEN C1 FETCH NEXT FROM C1 INTO @COLUMNNAME WHILE @@FETCH_STATUS <> -1     BEGIN         --SET @SQL = 'SELECT ''' + @TABLENAME + ''' AS TABLENAME,''' + @COLUMNNAME + ''' AS COLUMNNAME,* FROM ' + @TABLENAME + ' WHERE ' +  @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'''         SET @SQL = 'IF EXISTS(SELECT * FROM [' + @TABLENAME + '] WHERE [' +  @COLUMNNAME + '] LIKE ''%' + @SEARCHSTRING + '%'') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''' +  @COLUMNNAME + ''','' SELECT * FROM  [' + @TABLENAME + ']  WHERE [' + @COLUMNNAME + '] LIKE  ''''%' + @SEARCHSTRING + '%'''''') ;'         PRINT @SQL         EXEC (@SQL) FETCH NEXT FROM C1 INTO @COLUMNNAME     END CLOSE C1 DEALLOCATE C1  SELECT * FROM #RESULTS  GO CREATE PROCEDURE TABLEVIEWSEARCH2 @TABLENAME        VARCHAR(60),@SEARCHSTRING VARCHAR(50) -- EXEC TABLEVIEWSEARCH2 'GMACT','SOURCE' -- EXEC TABLEVIEWSEARCH2 'TABLEORVIEW','TEST' AS BEGIN SET NOCOUNT ON DECLARE @FINALSQL      VARCHAR(MAX), @COLUMNNAMES       VARCHAR(MAX) SET @FINALSQL = 'SELECT * FROM [' + @TABLENAME + '] WHERE 1 = 2 ' SELECT      @FINALSQL = @FINALSQL + ' OR [' + SYSCOLUMNS.NAME + '] LIKE ''%' + @SEARCHSTRING + '%'' '      FROM SYSCOLUMNS      WHERE OBJECT_NAME(id) = @TABLENAME     AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')     ORDER BY COLID  PRINT @FINALSQL EXEC(@FINALSQL) END --PROC 

I have tested this on an employee table containing the following data:

Running the following statement

EXEC TABLEVIEWSEARCH2 'employee','2'

resulted in:

2   1   eng2 4   2   dev2 7   3   sup2 9   4   qa2 

---

I thought I would provide some more example of this in action, since the Emp table above has only one field where it was searching the data.

This is a task table from a todo database:

Searching for the phrase en: (highlighted cells where data matched)

EXEC TABLEVIEWSEARCH2 'task','en'