Search code examples
sqlsql-serverinformation-schema

Search database for table with 2 or more specified column names

I have the following query that I use very frequently to find a table in a database that has a specified column name:

SELECT Table_Name, Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'db' AND COLUMN_NAME = 'col_A'

I'm now trying to find a table that has both of the specified columns in the query (ex: both col_A and col_B). I thought it would have been as simple as just further qualifying the WHERE clause, but that was to no avail. Any tips?

Solution

  • 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);

    And FWIW why I don't use INFORMATION_SCHEMA.