CODEWITHSUNDEEP
sql-servert-sqlinformation-schemadatabase-metadata
Neo
Neo

Reputation: 696

Find table containing two particular columns in SQL Server

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

Upvotes: 2

Views: 3191

Answers (3)

Stavr00
Stavr00

Reputation: 3314

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

One method just uses aggregation and having:

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
GROPU BY TABLE_NAME
HAVING SUM(CASE WHEN COLUMN_NAME LIKE '%A%' THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN COLUMN_NAME LIKE '%B%' THEN 1 ELSE 0 END) > 0;

If necessary, you can use join back to INFORMATION_SCHEMA.COLUMNS to get the column names -- although that is not your actual question.

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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)

Upvotes: 9

Related Questions