CODEWITHSUNDEEP
sql-serverssms
Luke Vanzweden
Luke Vanzweden

Reputation: 646

Is there a way to search all SQL tables by column name?

In this answer, you can search all tables for a column by column name.

Say I have a list of columns like this:

DECLARE @columnNames TABLE (Id varchar(30))

INSERT INTO @columnNames 
VALUES ('xColumn1Name'), ('xColumn2Name'), ('xColumn3Name')

I want to find all tables that have at least these three columns. Is it possible to do a foreach loop with the code below, or is there a simpler way?

SELECT      
    COLUMN_NAME AS 'ColumnName',  -- this code will get all tables with a column by name @xColumnName, but I would like to pass in a list
    TABLE_NAME AS 'TableName'
FROM        
    INFORMATION_SCHEMA.COLUMNS
WHERE       
    COLUMN_NAME LIKE '@xColumnName'
ORDER BY    
    TableName, ColumnName;

The table must have all 3 colums named in the list, and it would be cool if I could filter out tables that do not have a certain column or list of columns

Upvotes: 0

Views: 110

Answers (3)

Charlieface
Charlieface

Reputation: 72399

This is a relational division question. There are a few methods to solve this as Joe Celko writes. The common solution is as follows:

DECLARE @columnNames TABLE (Id varchar(30))

INSERT INTO @columnNames 
VALUES ('xColumn1Name'), ('xColumn2Name'), ('xColumn3Name')

select t.name
from sys.tables t
join sys.columns c on c.object_id = t.object_id
join @columnNames cn on cn.Id = c.name
group by t.object_id, t.name
having count(*) >=
    (select count(*) from @columnNames);

What this says is: give me all tables, where the number of columns which match the list @columnName is the same or more as the number in that list, in other words tehre is a match for every column.

Upvotes: 1

Robert van den Berg
Robert van den Berg

Reputation: 106

You could use INTERSECT to combine different result sets. This will give the records that are in all result sets, so in this case, the tables that have all three columns.

SELECT OBJECT_NAME(object_id) AS Table
FROM sys.columns
WHERE name = 'xColumn1Name'
INTERSECT
SELECT OBJECT_NAME(object_id) AS Table
FROM sys.columns
WHERE name = 'xColumn3Name'
INTERSECT
SELECT OBJECT_NAME(object_id) AS Table
FROM sys.columns
WHERE name = 'xColumn3Name'

Upvotes: 0

Kupokev
Kupokev

Reputation: 189

This should get your initial goal.

SELECT 
    [TableName]
FROM (
    SELECT      
        COLUMN_NAME AS 'ColumnName',  -- this code will get all tables with a column by name @xColumnName, but I would like to pass in a list
        TABLE_NAME AS 'TableName',
        ROW_NUMBER() OVER(PARTITION BY TABLE_NAME ORDER BY COLUMN_NAME) rn
    FROM        
        INFORMATION_SCHEMA.COLUMNS
    WHERE       
        COLUMN_NAME IN ('xColumn1Name', 'xColumn2Name', 'xColumn3Name')
) a
WHERE rn >= 3

For a short explanation, this query will look through the information schema to find any of these columns in a table. The ROW_NUMBER() then basically groups the columns by table. If there are 3 or more results (rn) then all 3 columns are there.

Since it is a sub select, you can also filter the outside select for particular columns if you want.

Upvotes: 1

Related Questions