CODEWITHSUNDEEP
sqlsql-serverinformation-schema
Crimp
Crimp

Reputation: 438

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?

Upvotes: 0

Views: 1212

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280340

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.

Upvotes: 3

nbk
nbk

Reputation: 49375

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

Upvotes: 1

Austin
Austin

Reputation: 2265

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'

Upvotes: 1

Related Questions