Search database for table with 2 or more specified column names-CodePudding

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?

CodePudding user response：

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

;WITH input(ColumnName) AS
(
  SELECT y FROM (VALUES(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.

CodePudding user response：

As long as you know the database already, this should work for you:

select t.TABLE_NAME
from INFORMATION_SCHEMA.TABLES t
inner join INFORMATION_SCHEMA.COLUMNS c
    on t.TABLE_NAME = c.TABLE_NAME
    and c.COLUMN_NAME = 'col_A'
inner join INFORMATION_SCHEMA.COLUMNS c2
    on t.TABLE_NAME = c2.TABLE_NAME
    and c2.COLUMN_NAME = 'col_B'

CodePudding user response：

If you want all column names and tables names that have both columnname you can do

SELECT Table_Name, Column_Name,TABLE_CATALOG
FROM INFORMATION_SCHEMA.COLUMNS
WHERE EXISTS( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = 'testdb' AND column_name = 'col_a')
AND EXISTS( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'testdb' AND column_name = 'col_b')