Apply Like Over All Columns Without Specifying All Column Names

Apply like over all columns without specifying all column names?

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

How to UPDATE all columns of a record without having to list every column

It's not possible.

What you're trying to do is not part of SQL specification and is not supported by any database vendor. See the specifications of SQL UPDATE statements for MySQL, Postgresql, MSSQL, Oracle, Firebird, Teradata. Every one of those supports only below syntax:

UPDATE table_reference
   SET column1 = {expression} [, column2 = {expression}] ...
[WHERE ...]

How to select all the columns of a table except one column?

You can use this approach to get the data from all the columns except one:-

1. Insert all the data into a temporary table
2. Then drop the column which you dont want from the temporary table
3. Fetch the data from the temporary table(This will not contain the data of the removed column)
4. Drop the temporary table

Something like this:

SELECT * INTO #TemporaryTable FROM YourTableName

ALTER TABLE #TemporaryTable DROP COLUMN Columnwhichyouwanttoremove

SELECT * FROM #TemporaryTable 

DROP TABLE #TemporaryTable 

Select statistical operations on table without knowing column names

Well, here is one fairly reasonable way to do such a thing:

Note You need to add the rest of the numeric data types to this example.

DECLARE @SQL nvarchar(max) = '';

SELECT @SQL = @SQL + 
'
UNION ALL
SELECT '''+ TABLE_NAME +''' As TableName, 
       '''+ COLUMN_NAME +''' As ColumnName, 
       MIN('+ COLUMN_NAME +') As [Min], 
       MAX('+ COLUMN_NAME +') As [Max], 
       STDEV('+ COLUMN_NAME +') As [STDEV]
FROM '+ TABLE_NAME
FROM information_schema.columns
WHERE DATA_TYPE IN('tinyint', 'smallint', 'int', 'bigint') -- Add other numeric data types 

SELECT @SQL = STUFF(@SQL, 1, 11, '') -- Remove the first `UNION ALL` from the query

EXEC(@SQL)

The result of this query will be structured like this:

TableName   ColumnName  Min     Max     STDEV
Table1      Col1        -123    543     100
Table1      Col2        54      72      5
Table1      Col3        0       1000    100

MySQL - Is it possible to use LIKE on all columns in a table?

You might want to look at the MATCH() function as well eg:

SELECT * FROM shoutbox 
WHERE MATCH(`name`, `foo`, `bar`) AGAINST ('$search')

You can also add boolean mode to this:

SELECT * FROM shoutbox 
WHERE MATCH(`name`, `foo`, `bar`) AGAINST ('$search') IN BOOLEAN MODE

You can also get the relevance scores and add FULLTEXT keys to speed up the queries.

Search database for table with 2 or more specified column names

Another way that satisfies the "2 or more" requirement without major modifications:

;WITH input(ColumnName) AS
(
  SELECT y FROM (VALUES

  /* simply list your columns here: */

    (N'col_A'),
    (N'col_B')

  ) AS x(y)
)
SELECT t.name FROM input
INNER JOIN sys.columns AS c ON c.name = input.ColumnName
INNER JOIN sys.tables  AS t ON c.[object_id] = t.[object_id]
GROUP BY t.name HAVING COUNT(*) = (SELECT COUNT(*) FROM input);

• Example db<>fiddle

And FWIW why I don't use INFORMATION_SCHEMA.

How can I get column names from a table in SQL?

MYSQL, MS SQL and Postgresql (thanks @christophe)

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE 
    TABLE_SCHEMA = 'database_name'
AND TABLE_NAME = 'table_name'

PIVOT the results if you need column names in one line

---

Related Topics