A Sql Question

[yanliu73]

How to find all the tables containing column ,say 'COL1',in a schema?
Is there a dictionay view containing all columns info?

Thanks

[oddster]

Try:

select table_name
from dba_tab_columns
where column_name='COL1';

Good luck!

[abhaysk]

Select 
  Distinct Table_Name
From
  DBA_TAB_COLUMNS
Where
  OWNER       = 'Schema_Name' AND
  Column_Name = 'COL1'
;

Abhay.

[stancho]

Originally posted by abhaysk

Select 
  Distinct Table_Name
From
  DBA_TAB_COLUMNS
Where
  OWNER       = 'Schema_Name' AND
  Column_Name = 'COL1'
;

Abhay.

abhaysk why distinct?

[sri_dba]

try this, u can get all info.

desc dba_tab_columns

[yanliu73]

That's what I want.
Thank you all so much

[abhaysk]

Originally posted by stancho
abhaysk why distinct?

I think, i need break or a cup of coffee will do

[yanliu73]

with sql:
select table_name
from dba_tab_columns
where column_name='COL1';

I get all tables and VIEWS that containing column 'COL1', if I only want the tables , not including views, what should i do?

[abhaysk]

Join with DBA_OBJECTS to elminate