CODEWITHSUNDEEP
sqlsql-server-2012
lonious
lonious

Reputation: 696

How to get a list of database objects based on a set of column names?

I'm testing whether or not database objects exist based on some criteria. I'd like to able to verify whether or not a database object exists or not based on a supposed set of unique column names. Is this feasible in SQL Server 2012? I'm interested in an answer even if my test becomes obsolete because of good business reasons. This was my first instinct:

SELECT c.name AS 'ColumnName', t.name as 'TableName'
FROM sys.columns c
    INNER JOIN sys.tables t 
        ON c.object_id = t.object_id
GROUP BY c.name, t.name
HAVING 
    c.name = 'column1'
    AND c.name = 'column2'
    AND c.name = 'column3'

From what I know about my current database I'm expecting this query to give me 3 records each displaying the expressed column and the same table name for all 3. However, this query gives me 0 results. I'm ignoring Views for now. Any suggestions?

Upvotes: 0

Views: 584

Answers (2)

lonious
lonious

Reputation: 696

I probably wasn't clear enough in my question. I actually needed to make sure that all of the results were coming from the same object. I was basically looking for something like this:

SELECT c.object_id, t.name
FROM sys.columns c
    INNER JOIN sys.tables t 
        ON c.object_id = t.object_id
WHERE
    c.name IN ('column1', 'column2', 'column3')
GROUP BY
    c.object_id, t.name
HAVING
    COUNT(c.object_id) = 3

Upvotes: 1

Niranjan Rajawat
Niranjan Rajawat

Reputation: 563

The query needs to be changed, you need to use IN operator. That being said, you also don't need a GROUP BY clause as there are no aggregation(sum, min, max etc) requirements. HAVING is generally used with a GROUP BY clause, therefore using WHERE here will fulfill your requirement. Try below code and see of you can get your desired results:

SELECT c.name AS 'ColumnName', t.name as 'TableName'
FROM sys.columns c
    INNER JOIN sys.tables t 
        ON c.object_id = t.object_id
WHERE 
    c.name in ('column1', 'column2', 'column3')

Upvotes: 0

Related Questions