CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Mahbubur Rahman Manik
Mahbubur Rahman Manik

Reputation: 5161

How do I list all tables in a schema having specific column in oracle sql?

I have found the list of all tables using this query -

SELECT DISTINCT OBJECT_NAME 
  FROM USER_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'

But I need to list all the tables having column name starttime_date or starttime.

Upvotes: 2

Views: 2901

Answers (2)

Yasir Arafat
Yasir Arafat

Reputation: 11

At first, you connect any schema then you could try to this SQL.

For example:

SELECT TABLE_NAME,
       COLUMN_NAME,
       DATA_TYPE,
       DATA_LENGTH,
       NULLABLE
  FROM all_tab_columns
 WHERE OWNER = 'schema_name' and column_name = 'column_name';

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

You could use USER_TAB_COLS view.

For example,

SQL> select table_name, column_name, data_type from user_tab_cols where column_name ='DEPTNO';

TABLE_NAME COLUMN_NAM DATA_TYPE
---------- ---------- ----------
DEPT       DEPTNO     NUMBER
EMP        DEPTNO     NUMBER

SQL>

You could try,

select * 
   from user_tab_cols
 where column_name in ('STARTTIME_DATE', 'STARTTIME');

Upvotes: 2

Related Questions