{"id":420,"date":"2009-07-29T09:38:04","date_gmt":"2009-07-29T14:38:04","guid":{"rendered":"http:\/\/appcrawler.com\/wordpress\/?p=420"},"modified":"2012-03-19T11:47:47","modified_gmt":"2012-03-19T16:47:47","slug":"using-dbms_metadata-to-get-table-and-index-definitions","status":"publish","type":"post","link":"http:\/\/appcrawler.com\/wordpress\/2009\/07\/29\/using-dbms_metadata-to-get-table-and-index-definitions\/","title":{"rendered":"Using DBMS_METADATA to get table and index definitions"},"content":{"rendered":"<p>Below is a quick script I use to extract table and index definitions I may need from time to time.  Enjoy&#8230;<\/p>\n<pre lang=\"plsql\" line=\"1\">\r\nset lines 255 trims on\r\nset serveroutput on size unlimited\r\ndeclare\r\n  l_tab_clob clob := empty_clob();\r\n  l_ind_clob clob := empty_clob();\r\nbegin\r\n  for cur in (select table_name from user_tables) loop\r\n    select dbms_metadata.get_ddl('TABLE',cur.table_name,user) into l_tab_clob from dual;\r\n    dbms_output.put_line(l_tab_clob);\r\n    for ind in (select index_name from user_indexes where table_name = cur.table_name) loop\r\n      select dbms_metadata.get_ddl('INDEX',ind.index_name,user) into l_ind_clob from dual;\r\n      dbms_output.put_line(l_ind_clob);\r\n    end loop;\r\n  end loop;    \r\nend;\r\n\/\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Below is a quick script I use to extract table and index definitions I may need from time to time. Enjoy&#8230; set lines 255 trims on set serveroutput on size unlimited declare l_tab_clob clob := empty_clob(); l_ind_clob clob := empty_clob();&hellip;<\/p>\n<p class=\"more-link-p\"><a class=\"more-link\" href=\"http:\/\/appcrawler.com\/wordpress\/2009\/07\/29\/using-dbms_metadata-to-get-table-and-index-definitions\/\">Read more &rarr;<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"footnotes":""},"categories":[19,22],"tags":[],"_links":{"self":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/420"}],"collection":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/comments?post=420"}],"version-history":[{"count":5,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/420\/revisions"}],"predecessor-version":[{"id":2020,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/420\/revisions\/2020"}],"wp:attachment":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/media?parent=420"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/categories?post=420"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/tags?post=420"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}