Find table containing two particular columns in SQL Server

Question

I would like to find all the tables containing two particular separate columns in SQL Server.

The first column name is "LIKE '%A%'" (Meaning it contains the substring "A") and the second column name is "LIKE '%B%'" (Meaning it contains the substring "B").

I wrote the following query and I would like to check its correctness:

SELECT s.TABLE_NAME 
FROM (SELECT COLUMN_NAME, TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE COLUMN_NAME LIKE '%A%'
    UNION
    SELECT COLUMN_NAME, TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE COLUMN_NAME LIKE '%B%') s
WHERE EXISTS (SELECT COLUMN_NAME, s.TABLE_NAME 
              FROM INFORMATION_SCHEMA.COLUMNS
              WHERE COLUMN_NAME LIKE '%A%')
AND EXISTS (SELECT COLUMN_NAME, s.TABLE_NAME
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE COLUMN_NAME LIKE '%B%');  

Answer 1

That should be easier:

SELECT s.TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES AS s
WHERE s.TABLE_TYPE='BASE TABLE'
AND EXISTS (SELECT 1 
              FROM INFORMATION_SCHEMA.COLUMNS
              WHERE TABLE_NAME=s.TABLE_NAME AND COLUMN_NAME LIKE '%A%')
AND EXISTS (SELECT 1
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME=s.TABLE_NAME AND COLUMN_NAME LIKE '%B%');  

UPDATE

with this code you will find all columns fitting both criterias as a list...

SELECT s.TABLE_NAME,listA,listB
FROM INFORMATION_SCHEMA.TABLES AS s
CROSS APPLY (SELECT STUFF(
             (
                 SELECT ', ' + COLUMN_NAME 
                 FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_NAME=s.TABLE_NAME AND COLUMN_NAME LIKE '%med%'
                 ORDER BY ORDINAL_POSITION
                 FOR XML PATH('')
             ),1,2,'')
             ) AS columnsWithA(listA)
CROSS APPLY (SELECT STUFF(
             (
                 SELECT ', ' + COLUMN_NAME 
                 FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_NAME=s.TABLE_NAME AND COLUMN_NAME LIKE 'kli%'
                 ORDER BY ORDINAL_POSITION
                 FOR XML PATH('')
             ),1,2,'')
             ) AS columnsWithB(listB)
WHERE s.TABLE_TYPE='BASE TABLE'
  AND listA IS NOT NULL AND listB IS NOT NULL

UPDATE 2

And with a final AND listA<>listB AND CHARINDEX(',',listA)=0 you would exclude identical listA and listB as long as there is only one column (=> no comma)

Answer 2

Set logic to the rescue!

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE COLUMN_NAME LIKE '%A%' 
AND COLUMN_NAME NOT LIKE '%B%'

INTERSECT

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE COLUMN_NAME LIKE '%B%' 
AND COLUMN_NAME NOT LIKE '%A%'

Updated to mutually exclude double matches. Removed inner join on TABLE schema