CODEWITHSUNDEEP
sqlsql-server
mreff555
mreff555

Reputation: 1121

Locating two columns within a table in an SQL database

I use this query frequently to retrieve all columns matching a string in a database. I am trying to link two tables and to do so I need matching columns to connect the two.

What I want to be able to search for is a table that has a column with the string "CUSTOMER_ID" AND another column with "WORKORDER_BASE_ID"

Is there an easy way to do this, working from this sqript? I thought some type of subquery might be necessary, but I have yet to make anything work.

SELECT 
    t.name AS table_name
    ,SCHEMA_NAME(schema_id) AS schema_name
    ,c.name AS column_name
    ,T.modify_date
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE 
    c.name LIKE '%CUSTOMER_ID%'                                       
ORDER BY modify_date

Upvotes: 0

Views: 59

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Something like this should work:

SELECT  t.name AS table_name ,
        SCHEMA_NAME(schema_id) AS schema_name ,
        c.name AS column_name ,
        T.modify_date
FROM    sys.tables AS t
        INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE   EXISTS ( SELECT *
                 FROM   sys.columns c1
                 WHERE  c1.object_id = c.object_id
                        AND c1.name = 'CUSTOMER_ID' )
        AND EXISTS ( SELECT *
                     FROM   sys.columns c1
                     WHERE  c1.object_id = c.object_id
                            AND c1.name = 'WORKORDER_BASE_ID' )
ORDER BY modify_date

Upvotes: 1

Related Questions