CODEWITHSUNDEEP
oracle-sqldeveloper
vijay
vijay

Reputation: 91

Is there any query to find table structure in Oracle_sqldeveloper

Hi i am new to oracle_sqldeveloper can you please give me the answer how to know the table structure and relationships of a database.

Upvotes: 9

Views: 36636

Answers (3)

neshkeev
neshkeev

Reputation: 6476

To get columns' and data type, try this:

select table_name, column_name, data_type 
  from all_tab_columns
 where table_name = <TABLE_NAME_HERE>
   and owner = '<YOUR_USER_HERE_IN_CAPITAL_LETTERS>'

If you have comments on your table then to get columns' comments:

select tc.table_name, tc.column_name, tc.data_type, cc.comments
  from all_col_comments cc, all_tab_columns tc
 where tc.table_name = <TABLE_NAME_HERE>
   and tc.owner = <OWNER_OF_TABLE_HERE>
   and tc.table_name = cc.table_name
   and tc.column_name = cc.column_name
   and tc.owner = cc.owner

If you are logged in under owner of the table you can write this:

select table_name, column_name, data_type 
  from user_tab_columns
 where table_name = <TABLE_NAME_HERE>

or to get columns with comments:

select tc.table_name, tc.column_name, tc.data_type, cc.comments
  from user_col_comments cc, user_tab_columns tc
 where tc.table_name = '<TABLE_NAME_HERE>'
   and tc.owner = '<YOUR_USER_HERE_IN_CAPITAL_LETTERS>'
   and tc.table_name = cc.table_name
   and tc.column_name = cc.column_name

To get relationships between tables use this query:

select uc1.table_name
     , uc1.constraint_name
     , cc1.column_name
     , uc2.table_name r_table_name
     , uc2.constraint_name r_constraint_name
     , cc2.column_name r_column_name
  from all_constraints uc1
     , all_constraints uc2
     , all_cons_columns cc1
     , all_cons_columns cc2
 where 1 = 1
   and uc2.constraint_type = 'R'
   and uc1.constraint_name = uc2.r_constraint_name
   and cc1.table_name = uc1.table_name
   and cc1.constraint_name = uc1.constraint_name
   and cc2.table_name = uc1.table_name
   and cc2.constraint_name = uc1.constraint_name
   and uc1.owner = '<YOUR_USER_HERE_IN_CAPITAL_LETTERS>'
   and uc2.owner = uc1.owner
   and cc1.owner = uc1.owner
   and cc2.owner = uc1.owner
 order by 1

Columns with the "R_" prefix mean that they are foreign data (they represent foreign keys). As you can see, I used the tables with the "ALL_" prefix, to use similar tables with the "USER_" prefix, get rid of the "OWNER" section. To know more about oracle data dictionary read this.

Upvotes: 7

jaibalaji
jaibalaji

Reputation: 3475

1) type your table name. 2) right click on table name & click Open Declaration.

Upvotes: 4

apomene
apomene

Reputation: 14389

You can try

DESC <table_name>

Upvotes: 10

Related Questions