Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've got a database with about 100 tables and I need to build a join query to get specific data from two of them. I know one but not the other. Basically I need something like:

select <tables> from <database> where exists table.column name;

How can I do this?

share|improve this question
5  
My answer assumed SQL Server. Is that the RDBMS you're working with? –  Thomas Stringer Jun 18 '13 at 19:05
1  
When asking questions it is better to check back and respond to any requests for clarification. –  Martin Smith Jun 19 '13 at 9:47
add comment

3 Answers

    select *
from INFORMATION_SCHEMA.columns
where column_name = '<your column name>'
share|improve this answer
3  
Why not to use INFORMATION_SCHEMA, by our very own @AaronBertrand (sqlblog.com/blogs/aaron_bertrand/archive/2011/11/03/…) –  Thomas Stringer Jun 18 '13 at 19:55
    
@ThomasStringer I agree but the OP has not tagged the question with any dbms tag. –  ypercube Jun 19 '13 at 9:05
add comment

The below query should give you what you're looking for:

use YourDatabase;
go

select
    object_schema_name(t.object_id) + '.' + t.name as table_name,
    c.name as column_name
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
where c.name like '%ColumnSearchText%';

If you're looking for columns of an exact name, just replace the WHERE clause with:

where c.name = 'ColumnSearchText';
share|improve this answer
add comment

For IBM DB2 you would use the following:

select tabschema,tabname from syscat.columns where colname = 'COLUMN_NAME'

Note that in DB2, column names will be in upper case unless they were defined inside of double quotes with something other than upper case. Then you have to supply the exact casing of the column name as well.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.