使用查询获取VIEW DDL [英] Get VIEW ddl using query

问题描述

对于数据库重新架构，我需要获取每个表的DDL并在数据库(Oracle)中进行查看.我不想转到每个表/视图的属性并在SQL Developer中从中获取SQL.

For database re-architecture I need to get DDL of each table and view in the database(Oracle). I don't want to go to property of each table/view and get SQL out of it in SQL Developer.

我成功使用表获得了DDL-

I successfully got DDL for table using-

select dbms_metadata.get_ddl('TABLE','Table_name','Schema_Name') 
  from dual;

但是面对VIEW和MVIEW的问题.任何人都可以为表格以外的元素提供命令/关键字.

But facing problem with VIEW and MVIEW. Could anyone provide commands/keywords for elements other than table.

此外，我想将结果导出到excel文件中，第一列为TableName，第二列为DDL.

Also, I want to export the result in an excel file with first column as TableName and second column as DDL.

推荐答案

尝试通过以下查询获取视图:

Try the below query for view:

select text from ALL_VIEWS where upper(view_name) like upper(<view_name>);

对于mviews:

select query from ALL_MVIEWS where upper(mview_name) like upper(<mview_name>);

这篇关于使用查询获取VIEW DDL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！