using mast db to get table names using column names

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
ac_davis2002Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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>
0
 
Atdhe NuhiuConnect With a Mentor Commented:
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
0
 
Lee SavidgeConnect With a Mentor Commented:
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')
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Lee SavidgeCommented:
Obviously you can alter the where clause:

where c.column_name like '%colname%'

where c.column_name = 'mycolname'

etc.
0
 
Atdhe NuhiuCommented:
Obviously your where clause can be whatever you need

where C.name = 'column_name'
0
 
ac_davis2002Author Commented:
thanks!!
0
 
Atdhe NuhiuCommented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.