using mast db to get table names using column names

ac_davis2002
ac_davis2002 used Ask the Experts™
on
hi

I have been given the column names i need to use but not the table names they belong to.

can anyone give me a query to search the system tables to get the table names where column names like 'cloumn name'

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I use this when looking for field names


Select
  O.name objectName,
  C.name ColumnName
from sys.columns C

inner join sys.objects O
  ON C.object_id=O.object_id

where 1=1
--and C.name like '%renew%'
and C.name like '%reason%'

order by O.name,C.name
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
This will show you all tables that have ALL the column names you specify:

SELECT o.name
FROM sys.objects o
INNER JOIN sys.columns c ON
    c.object_id = o.object_id
WHERE
    c.name IN ('col_name1', 'col_name2', ...)
GROUP BY
    o.name
HAVING
    COUNT(*) = <number_of_col_names>
select t.table_name, c.column_name from information_schema.columns c
join information_schema.tables t
on c.table_name = t.table_name
and table_type = 'base table'
where column_name in ('mycolname', 'mycolname2')
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Obviously you can alter the where clause:

where c.column_name like '%colname%'

where c.column_name = 'mycolname'

etc.
Obviously your where clause can be whatever you need

where C.name = 'column_name'

Author

Commented:
thanks!!
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial