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.

Solution 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

Solution 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'

Solution 3

No, this is not possible with SQL. It would be considered bad practice as well, although I can see the use case in your scenario. Your best bet is to script it in your favorite language by retrieving a list of all column names and then executing either a separate query with a like for each column or a single large query that combines it all:

select
    *
from
    a
where
    a.column_1 like '%blah%' or 
    a.column_2 like '%blah%';

or, separate queries:

select
    *
from 
    a 
where 
    a.column_1 like '%blah%'

select
    *
from 
    a 
where 
    a.column_2 like '%blah%'

Solution 4

You can try something like this but if your table is really big you might have some trouble because it will create an XML of your entire table and then query the XML for the search string. The output is the column name(s) where the string is found.

;with C(TableXML) as
(
  select *
  from YourTable
  for xml path('T'), type
)
select distinct T.X.value('local-name(.)', 'sysname') as ColumnName
from C
  cross apply C.TableXML.nodes('/T/*') as T(X)
where T.X.value('.', 'varchar(max)') like '%x%'

https://data.stackexchange.com/stackoverflow/query/58934/new

Solution 5

Thanks Nanda :)

here is my slimmed down script:

use a_database

declare 
    @TableName as nvarchar(50) = 'a_table',
    @FilterContition as nvarchar(50) = 'like ''%x%''',
    @ColumnName as nvarchar(100),
    @ColumnCursor as cursor,
    @Sql as nvarchar(4000)

set @ColumnCursor = cursor for
    select distinct c.name
    from sys.objects as o
    inner join sys.columns as c
        on o.object_id = c.object_id
    where o.name = @TableName
    and type_name(c.user_type_id) in ('VARCHAR','NVARCHAR','CHAR','NCHAR')

open @ColumnCursor
fetch next from @ColumnCursor into @ColumnName 
set @Sql = 'select * from ' + @TableName + ' where ' + @ColumnName + ' ' + @FilterContition
while @@fetch_status = 0
begin
    fetch next from @ColumnCursor into @ColumnName
    set @Sql = @Sql + ' and ' + @ColumnName + ' ' + @FilterContition
end
close @ColumnCursor
deallocate @ColumnCursor

exec(@Sql)

it uses: - dynamic sql - a cursor - database metadata

View more solutions

Comments

[philshem]

This is an interesting question. My background is with bash commands, so I think of getting data by (1) grep and then (2) awk. In this manner, you grep a lot (even too much), and then filter with additional grep or awk statements. I was surprised that SQL had no built-in 'grep' functionality.

[Michael A]

Performance isn't such an issue here as I'm the sole user on a dev database and this is a one off task, appreciate the heads up though

[Oleg Dok]

If so - then or dynamic querying - and you even not have to know column names, or my variant

[Michael A]

Took me a bit to understand what you're doing here but now that I do I love this solution. Appreciated!