如何在Oracle中通过SQL获取表注释? [英] How to get table comments via SQL in Oracle?
问题描述
我尝试过:
select * from user_tab_comments;
它返回3列"TABLE_NAME","TABLE_TYPE"和"COMMENTS",但是"TABLE_NAME"列就像"encrypted"一样,我需要清除表名:
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
当我使用select * from user_tables;
时,TABLE_NAME未被加密".
When I use select * from user_tables;
TABLE_NAME is not "encrypted".
推荐答案
由于10g,当我们发出DROP TABLE语句时,Oracle不会立即删除表.而是像这样BIN$IN1vjtqhTEKcWfn9PshHYg==$0
重命名它们并将它们放入回收站.这使我们能够恢复原本不想删除的表. 了解更多信息.
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.
回收站中的表仍然是表,因此它们显示在ALL_TABLES和类似视图中.因此,如果您只想查看仅与活动(未删除)表相关的注释,则需要按表名称进行过滤:
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$'
/
我不敢相信没有标志列,所以您可以这样做,并且is_recycled = 0或其他."
"I can't believe there isn't a flag column so you could do and is_recycled = 0 or something. "
您是对的,这太不可思议了.因此我检查了文档,结果发现Oracle 10g在USER_/ALL_/DBA_TABLES视图中添加了一个名为DROPPED的列.
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'
/
查看文档.显然,加入ALL_TABLES视图需要比对名称进行过滤更多的键入操作,因此根据我们的需要,保留原始WHERE子句可能会更容易.
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.
这篇关于如何在Oracle中通过SQL获取表注释?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!