Creating HTML Schema Doc Generator Reports

Beginning with TOAD version 7.3, the HTML Schema Doc Generator is the most flexible and featured method for producing database reports. Moreover, TOAD 7.4 adds major performance enhancements for generating HTML reports against schemas with large numbers of database objects. In fact, the QA test scenario was based on an ERP application with many thousands of objects. Plus, the Schema Doc Generator can be run from the command line. Therefore TOAD can now fully automate your entire HTML report generation process via the Windows scheduler.

The HTML Schema Doc Generator screen is accessible from the main menu at Tools, HTML Schema Doc Generator and is shown in Figure 7.6. This screen requires that you visit five tabs in order to specify your entire database reporting needs prior to the report execution. The first tab, Sources, is shown in Figure 7.6. This screen has two sides. The left-hand side permits you to select the schema you want to report on. You may select more than one schema, plus there are context menu options for Check All, Check None, Check My Schema, and Check My Schema only. The right-hand side of the screen is where you can pick TOAD .def files as the offline data dictionary source for the HTML reports. These .def files are created via the Generate Schema Script utility (documented in Chapter 6). Note that you also must have selected the check box for Def File Will Be Used for HTML Schema Doc Generation (which makes that utility run a bit slower than if merely exporting the basic metadata for script generation).

Figure 7.6. TOAD HTML Schema Doc Generator ” Sources tab.

The second tab, Content, shown in Figure 7.7 permits you to select all the summaries and objects that should be included in the HTML report. For summaries, TOAD merely generates an HTML table with object counts for all those types you request. For descriptions, TOAD simply generates different detailed output based on that object type (for example, describe-like HTML table for tables, a simple HTML text box for PL/SQL procedures, and so on). Also note that this screen offers context menu options for Select All and Select None. This may not be readily apparent because this is not really a Windows norm.

Figure 7.7. TOAD HTML Schema Doc Generator ”Content tab.

The third tab, Format, shown in Figure 7.8 permits you to specify numerous HTML report generation control parameters. This tab is quite involved and clearly where you should spend most of your customization time and effort. There are numerous options on this tab that are fairly self-explanatory, such as the file options: background, font selections, link colors, and HTML table options. The combinations of these selections provide you with almost limitless HTML formatted output results. However, the Use Existing CSS File check box requires some explanation. When the actual HTML report file is generated, TOAD also generates a cascading style sheet file (.css) with the same name as the report to contain all your format selections. So if you want to reuse one of your previous report's format selections on another report, you simply check this box and then select that report's .css file. Note that this will disable many of the selections on this screen because you're using the format information from that other cascading style sheet file.

Figure 7.8. TOAD HTML Schema Doc Generator ”Format tab.

The fourth tab, In/Exclusions, shown in Figure 7.9 permits you to specify complex inclusion and exclusion filter files for tables, views, and procedures. First, you merely select the Create Filter File button to create a base filter file. This is simply a text file that contains the names for the selected schemas. Then you edit that file to either add or remove object names, or better yet to use pattern-matching filtration criteria. For example, you can specify STARTSWITH: string, ENDSWITH: string, or CONTAINS: string. In Figure 7.9, the table exclusion file will eliminate tables with names that start with CUST and end with YEE (which will filter out customer and employee tables). Note that the Procs button will only be displayed after you select to either include or exclude some tables or views via a file. Then you can specify whether or not to include only procedures dependent on the tables that also pass the filters.

Figure 7.9. TOAD HTML Schema Doc Generator ”In/Exclusions tab.

The fifth tab, Headers/Footers, shown in Figure 7.10 permits you to specify up to four header and footer lines for each file created by the HTML Schema Doc Generator. You may also modify each line's font selection by merely clicking the label for Font next to each line (which is actually a font button, but does not appear as such until you hover your mouse over it).

Figure 7.10. TOAD HTML Schema Doc Generator ” Headers/ Footers tab.

After you've visited the various tabs and have made all your selections, you click the Begin HTML Schema Doc Generation button in the upper left of the screen's toolbar to initiate generating the report. If you've selected a large schema or multi-selected numerous schemas, generating the report may require more than a few seconds. After the HTML report has been completely generated TOAD will ask you if you want to view it. If so, TOAD will open the generated HTML report file using your default Web browser and starting with the report's Table of Contents page, as shown in Figure 7.11.

Figure 7.11. TOAD HTML Schema Doc Generator ”Table of Contents page.

The TOAD HTML report is broken down into various sections, with links to navigate the entire report. The Table of Contents page contains links to each of the database object type sections in the report (which you previously selected for generation via the Contents tab). So for example, selecting Tables takes you to the HTML table listing for the database tables shown in Figure 7.12. What you see here is simply an HTML table listing all the tables found along with some very basic data dictionary information for each. Furthermore, note that each of the table names in that HTML table live links for further drill-down into the generated report.

Figure 7.12. TOAD HTML Schema Report ”table of tables.

Thus if you're interested in drilling down into one of those tables previously shown, then selecting its link takes you to that table's detailed information section, shown in Figure 7.13. The table is shown in a describe-like fashion, with the corresponding index information in an HTML table that follows . The whole report is interconnected and easy to navigate, as these examples show. As such, many DBA's find that publishing such HTML reports internally on their companies' intranet web sites helps developers and business analysts to better perform their jobs without having to issue numerous and ongoing queries against the production database's data dictionary. In short, these reports become their offline data dictionary published for general use.

Figure 7.13. TOAD HTML Schema Report ”specific table.

Ideally, you'd like to have this HTML report generated and waiting for you each morning in your HTML report directory. That way you could start your day by quickly checking the HTML Schema Doc report output for your databases. To accomplish this, you must schedule this report to run on your Windows machine via the Microsoft Task Scheduler as shown in Figure 7.14. For Windows 2000, this is located at Start, Settings, Control Panel, Scheduled Tasks, Add Scheduled Task.

Figure 7.14. Windows Scheduling of TOAD HTML Schema Doc Generator.

Note that you're simply scheduling Windows on your PC to run TOAD in command-line mode, where you provide the connection information and an HTML schema doc generate (that is, GENHTML) command text file. To produce that file is very simple. You merely need to open the HTML Schema Doc Generator screen, make all your selections, and then choose the Save Settings to File toolbar icon. The generated file will contain everything necessary to start TOAD, connect to the database, and then run this screen. The generated command file will also contain comments on some commands you can add to tailor its behavior (for example, to close the screen and also shut down when the process is complete).