Solved

using mast db to get table names using column names

Posted on 2012-03-16
7
373 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:ac_davis2002
7 Comments
 
LVL 13

Assisted Solution

by:Philip Pinnell
Philip Pinnell earned 167 total points
ID: 37729395
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 167 total points
ID: 37729399
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
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 166 total points
ID: 37729416
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 25

Expert Comment

by:Lee Savidge
ID: 37729428
Obviously you can alter the where clause:

where c.column_name like '%colname%'

where c.column_name = 'mycolname'

etc.
0
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 37729438
Obviously your where clause can be whatever you need

where C.name = 'column_name'
0
 

Author Closing Comment

by:ac_davis2002
ID: 37730106
thanks!!
0
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 37730121
thanks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now