如何在不使用DESCRIBE命令的情况下在Oracle中描述表? [英] How can I describe a table in Oracle without using the DESCRIBE command?

问题描述

我要上的课很难.我们需要编写一个Oracle脚本，该脚本的行为就像DESCRIBE命令一样.我们正在使用的这本书非常糟糕地描述了如何使用数据字典.不是寻找答案，而是指向正确的方向.

I'm having a hard time with a class I am taking. We need to write an Oracle script that will act just like the DESCRIBE command. The book we are using describes how to work with the Data Dictionary very poorly. Not looking for answers, but a point in the correct direction.

推荐答案

您在寻找 ALL_TAB_COLUMNS -除了用户有权查看的所有表外，其他均相同.

You're looking for USER_TAB_COLUMNS - all the columns, and their descriptions in the schema the query is executed in - or ALL_TAB_COLUMNS - the same except for all tables that user has permission to view.

典型的查询可能是:

select *
  from user_tab_columns
 where table_name = 'MY_TABLE'
 order by column_id

column_id是表中列的顺序".

除非您一直使用大写字母添加表(这是一个坏主意)，否则应确保大写"MY_TABLE"，在这种情况下，您需要使用类似= "MyTable"的名称.

You should ensure that 'MY_TABLE' is capitalised unless you've been adding tables with casing ( a bad idea ) in which case you need to use something like = "MyTable".

特别地，desc等同于我从好的Oracle资源 ss64 中偷走的以下内容:

Specifically desc is equivalent to the following which I stole from ss64, a good Oracle resource:

select column_name as "Name"
     , nullable as "Null?"
     , concat(concat(concat(data_type,'('),data_length),')') as "Type"
  from user_tab_columns
 where table_name = 'MY_TABLE';

您可以通过select * from dictionary找到所有此类视图，这是文档 a>.

You can find all of this sort of view by select * from dictionary, which is the top level of the data dictionary or by looking at the documentation.

还有DBA_TAB_COLUMNS，它与ALL_TAB_COLUMNS相同，但适用于数据库中的每个表.这假定您具有查看它和表的特权.如果您无权访问此表，则需要让您的DBA授予您SELECT ANY DICTIONARY特权.

There is also the DBA_TAB_COLUMNS, which is the same as ALL_TAB_COLUMNS, but for every table in the database. This assumes that you have the privileges to view both it and the tables. If you do not have access to this table you need to get your DBA to grant you the SELECT ANY DICTIONARY privilege.

这篇关于如何在不使用DESCRIBE命令的情况下在Oracle中描述表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！