# This is part of an ArsDigita Community System 3.x module # called the Schema and Data Browser, available here: # http://eveander.com/arsdigita/acs-repository/sdb ad_page_contract { Shows the definition for all tables, views, sequences, triggers, and PL/SQL objects in a database. @author eveander@eveander.com @creation-date 18 October 2002 @cvs-id $Id: objects-all.tcl,v 1.2 2002/10/25 18:17:31 eve Exp $ } { } set title "All database objects and their definitions" ReturnHeaders ns_write "[ad_admin_header $title] <h2>$title</h2> [ad_admin_context_bar [list "/admin/sdb/" "Schema and Data Browser"] "All Database Objects"] <hr> <h3>Tables</h3> <ul> " # put table_names into a list and then loop through them w/tcl to avoid running # out of database handles set table_name_list [list] db_foreach get_all_table_names "select table_name from user_tables order by table_name" { lappend table_name_list $table_name } foreach table_name $table_name_list { ns_write "<li><font color=009900>$table_name</font>" # see if there are any table-level comments set table_comments "" db_foreach get_table_level_comments "select comments from user_tab_comments where table_name = :table_name and comments is not null" { append table_comments "$comments<p>" } ns_write "$table_comments <table border=1> <tr><th>column_name</th><th>data_type</th><th>data_length</th><th>data_scale</th><th>data_precision</th><th>nullable</th><th>data_default</th><th>constraints</th><th>comments</th></tr>" db_foreach get_all_columns "select column_name, data_type, data_length, data_scale, data_precision, nullable from user_tab_columns where table_name = :table_name" { # get data_default, which is a long (so our Oracle driver can't select it directly) set data_default [db_exec_plsql get_data_default "begin :1 := sdb_get_col_data_default(:table_name,:column_name); end;"] # see if there are any row-level comments set row_comments "" db_foreach get_row_level_comments "select comments from user_col_comments where table_name = :table_name and column_name = :column_name and comments is not null" { append row_comments "$comments<p>" } # get constraints set constraint_list [list] db_foreach get_constraints "select ucc.constraint_name, uc.constraint_type, uc.r_constraint_name from user_cons_columns ucc, user_constraints uc where ucc.constraint_name = uc.constraint_name and ucc.table_name = :table_name and ucc.column_name = :column_name" { # constraint_type can be: # C: check # R: references # P: primary key # U: unique if { $constraint_type == "C" } { set search_condition [db_exec_plsql get_search_condition "begin :1 := sdb_get_con_search_condition(:constraint_name); end;"] lappend constraint_list "$constraint_name: check $search_condition" } elseif { $constraint_type == "R" } { set r_table_name [db_string get_table_referenced "select table_name from user_constraints where constraint_name = :r_constraint_name"] lappend constraint_list "$constraint_name: references $r_table_name" } elseif { $constraint_type == "P" } { lappend constraint_list "$constraint_name: primary key" } elseif { $constraint_type == "U" } { lappend constraint_list "$constraint_name: unique" } else { lappend constraint_list "$constraint_name: unknown constraint of type $constraint_type" } } set constraints [join $constraint_list "<br>"] ns_write "<tr><td>$column_name</td><td>$data_type</td><td>$data_length</td><td>[ec_nbsp_if_null $data_scale]</td><td>[ec_nbsp_if_null $data_precision]</td><td>$nullable</td><td>[ec_nbsp_if_null $data_default]</td><td>[ec_nbsp_if_null $constraints]</td><td>[ec_nbsp_if_null $row_comments]</td></tr>" } ns_write "</table> <p> Indices: <ul> " set old_index_name "" set column_list [list] db_foreach get_all_indices "select index_name, column_name from user_ind_columns where table_name=:table_name order by index_name" { if { $old_index_name != $index_name && ![empty_string_p $old_index_name] } { ns_write "<li>$old_index_name on [join $column_list ", "]" set column_list [list] } lappend column_list $column_name set old_index_name $index_name } # write out last index if { ![empty_string_p $old_index_name] } { ns_write "<li>$old_index_name on [join $column_list ", "]" } else { ns_write "\[none\]" } ns_write "</ul> <p> " } ns_write "</ul> <h3>Views</h3> <ul> " db_foreach get_all_views "select view_name from user_views order by view_name" { ns_write "<li><font color=009900>$view_name</font> <p> <pre> " # get text, which is a long set text [db_exec_plsql get_text "begin :1 := sdb_get_view_text(:view_name); end;"] ns_write "$text </pre> <p> " } ns_write "</ul> <h3>Sequences</h3> <ul> " db_foreach get_all_sequences "select sequence_name, min_value, max_value, increment_by, cycle_flag, order_flag, cache_size from user_sequences order by sequence_name" { set sequence_def "create sequence $sequence_name" if { $increment_by > 0 } { if { $increment_by != 1 } { append sequence_def "<br>increment by $increment_by" } if { [string compare $min_value "1"] != 0 } { append sequence_def "<br>minvalue $min_value" } if { [string compare $max_value "999999999999999999999999999"] != 0 } { append sequence_def "<br>maxvalue $max_value" } } else { append sequence_def "<br>increment by $increment_by" if { [string compare $min_value "-99999999999999999999999999"] != 0 } { append sequence_def "<br>minvalue $min_value" } if { [string compare $max_value "-1"] != 0 } { append sequence_def "<br>maxvalue $max_value" } } if { $cycle_flag == "Y" } { append sequence_def "<br>cycle" } if { $order_flag == "Y" } { append sequence_def "<br>order" } if { $cache_size != 20 } { append sequence_def "<br>cache $cache_size" } ns_write "<li>$sequence_def;<p>\n" } ns_write "</ul> <h3>Triggers</h3> <ul> " db_foreach get_all_triggers "select trigger_name, trigger_type, triggering_event, table_name, when_clause, description from user_triggers order by trigger_name" { # get trigger_body, which is a long set trigger_body [db_exec_plsql get_trigger_body "begin :1 := sdb_get_trigger_body(:trigger_name); end;"] ns_write "<li><font color=009900>$trigger_name</font> <p> Trigger Type: $trigger_type<br> Triggering Event: $triggering_event<br> Table Name: $table_name<br> When Clause:<blockquote><pre>$when_clause</pre></blockquote> Description:<blockquote><pre>$description</pre></blockquote> Trigger Body:<blockquote><pre>$trigger_body</pre></blockquote> <p> " } ns_write "</ul> <h3>PL/SQL</h3> <ul> " db_foreach get_all_plsql_objects "select name, type from user_source group by name, type order by name" { ns_write "<li><font color=009900>$name</font> ($type) <blockquote><pre> " db_foreach get_lines "select text from user_source where name=:name and type=:type order by line" { # text appears to always end with a newline, so we don't have to add one ns_write "$text" } ns_write "</pre></blockquote>" } ns_write "</ul> [ad_admin_footer] "