Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get table comments via SQL in Oracle?

Tags:

sql

oracle

I've tried :

select * from user_tab_comments; 

and it returns me 3 columns "TABLE_NAME", "TABLE_TYPE", and "COMMENTS", but the "TABLE_NAME" column is like "encrypted", I need clear table names :

TABLE_NAME                      TABLE_TYPE  COMMENTS  BIN$IN1vjtqhTEKcWfn9PshHYg==$0  TABLE       Résultat d'intégration d'une photo numérisée BIN$PUwG3lb3QoazOc4QaC1sjw==$0  TABLE       Motif de fin d'agrément de maître de stage 

When I use select * from user_tables; TABLE_NAME is not "encrypted".

like image 905
Tristan Avatar asked May 15 '13 13:05

Tristan


People also ask

How do I view comments in Oracle SQL?

You can view the comments on a particular table or column by querying the data dictionary views USER_TAB_COMMENTS , DBA_TAB_COMMENTS , or ALL_TAB_COMMENTS or USER_COL_COMMENTS , DBA_COL_COMMENTS , or ALL_COL_COMMENTS . Specify the name of the operator to be commented.

How do you comment a table in SQL?

If you specify a database, it must be the current database. The name of the table with which to associate the comment. Specifies the text of the comment to add. Enclose the text of the comment within single-quotes.

What is the shortcut for comment in Oracle SQL Developer?

In SQL Developer you can comment a line or a block using "Source -> Toggle Line Comments" (Ctrl-Slash), but it would be nice to have a button that allows you to do it not only for line or block, but also for a part of a line.


1 Answers

Since 10g Oracle doesn't immediately drop tables when we issue a DROP TABLE statement. Instead it renames them like this BIN$IN1vjtqhTEKcWfn9PshHYg==$0 and puts them in the recycle bin. This allows us to recover tables we didn't mean to drop. Find out more.

Tables in the recycle bin are still tables, so they show up in ALL_TABLES and similar views. So if you only want to see comments relating only to live (non-dropped) tables you need to filter by table name:

select * from all_tab_comments where substr(table_name,1,4) != 'BIN$' / 

"I can't believe there isn't a flag column so you could do and is_recycled = 0 or something. "

You're right, it would be incredible. So I checked the documentation it turns out Oracle 10g added a column called DROPPED to the USER_/ALL_/DBA_TABLES views.

select tc.*  from all_tab_comments tc      join all_tables t      on tc.owner = t.owner      and tc.table_name = t.table_name where t.dropped = 'NO' / 

Check out the documentation. Obviously the need to join to the ALL_TABLES view requires more typing than filtering on the name, so depending on our need it might just be easier to keep the original WHERE clause.

like image 126
APC Avatar answered Oct 12 '22 23:10

APC