14

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?

1
  • 6
    My answer assumed SQL Server. Is that the RDBMS you're working with? Jun 18, 2013 at 19:05

8 Answers 8

23

Using information_schema

This is the standards-compliant cross-RDBMS way to do it.

SELECT table_catalog, table_schema, table_name, column_name
FROM INFORMATION_SCHEMA.columns
WHERE column_name = '<your column name>';

You can see this documented

4
  • 5
    Why not to use INFORMATION_SCHEMA, by our very own @AaronBertrand (sqlblog.com/blogs/aaron_bertrand/archive/2011/11/03/…) Jun 18, 2013 at 19:55
  • 2
    @ThomasStringer I agree but the OP has not tagged the question with any dbms tag. Jun 19, 2013 at 9:05
  • @ThomasStringer Use a real database, right off the bat PostgreSQL has supported Identity Columns in the information_schema.columns since 2006. A full decade before it even implemented them. If Microsoft SQL doesn't support the standard, perhaps we should be looking at nagging them. Others do. Oct 23, 2017 at 16:51
  • note: this also returns views columns so add to check for table type
    – edelwater
    Feb 15 at 14:43
5

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.

1
  • 1
    If the column where defined using quotes (which should be avoided), you can use UPPER or UCASE function to transform the colname to upper case: WHERE UPPER(colname) = 'COLUMN_NAME'.
    – Lennart
    Oct 23, 2017 at 16:44
3

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';
0

in Teradata 15:

SELECT DATABASENAME||'.'||TABLENAME AS FULL_TABLENAME,
COUNT(1) AS NUMBER_OF_COLUMNS
FROM DBC.COLUMNSV
WHERE 1 = 1
AND COLUMNNAME LIKE '%<YOUR COLUMNNAME HERE>%'
GROUP BY 1
0
0

Oracle sql / plsql:

select table_name from all_tab_columns where column_name='yourcolumnname';
0
SELECT * FROM _v_sys_columns WHERE COLUMN_NAME='$COLUMN_NAME'

Please pass the column name to this variable: $COLUMN_NAME

-3

//Select the Particular table:

SYNTAX:
       SELECT COLUMN_NAME FROM TABLE_NAME WHERE COLUMN_NAME='VALUE';
EXAMPLE:
       SELECT PERSON_NAME FROM PERSON WHERE PERSON_ID=1;
1
  • You probably misread the OP's question, your answer does not answer the question that was asked.
    – Lennart
    May 26, 2016 at 16:25
-3

For SQL Server:

SELECT name 
FROM sysobjects 
WHERE id IN 
( 
    SELECT id 
    FROM syscolumns 
    WHERE name = 'EXACT_COLUMN_NAME_TO_SEARCH'
)
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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