Generating Schema Scripts

Ever needed to reverse-engineer a schema? In the old days when there were just tables, indexes, and views, DBAs simply had a script to reproduce their Data Description Language (DDL) from the online data dictionary. Of course, nowadays there are so many convoluted object types and interdependencies among them that you can't use simple, home-grown scripts any more. So some DBAs will attempt to use data modeling tools like Quest Software's QDesigner or CA's ERwin, but these tools often don't support the more physical aspects of DDL, such as tablespaces and partitions. So what's the poor DBA to do? TOAD comes to the rescue with the perfect solution with the Generate Schema Script screen. Plus this screen can be run from the command line. So TOAD can now fully automate your database reverse-engineering tasks by using the Windows scheduler.

TOAD's Generate Schema Script is accessible from the main menu at DBAGenerate Schema Script. This screen takes you through a simple four-step process to reverse-engineer all your schema's DDL.

The first step is the Source and Destination tab shown in Figure 6.38, which permits you to specify the three major items: source, schema, and file. For source, you indicate whether to reverse-engineer from an online data dictionary or a TOAD schema definition file (a TOAD proprietary format for containing an offline data dictionary and usable by several other TOAD screens). For schema, you select those schemas to reverse-engineer. And for file, you provide the filename to contain either the SQL or TOAD schema definition. Note that if you select multiple schemas and you're generating a DDL file, TOAD has a check box to create a separate DDL file per schema.

Figure 6.38. TOAD Generate Schema Script ”Source and Destination.

The second step is the Object Types tab shown in Figure 6.39, which permits you to specify the object types to reverse-engineer. These settings apply to all schemas that are being reverse-engineered. So if you want different settings for different schemas, you'll need to make separate runs for each. Also note that this screen offers context menu options for Select All and Select None. This may not have been readily apparent because this is not really a Windows norm.

Figure 6.39. TOAD Generate Schema Script ”Object Types.

The third step is the Script Options tab shown in Figure 6.40, which enables you to define various SQL code generation options. All but three of these are fairly self-explanatory and require no explanation. The "Exceptions Into" Table field allows you to name the exception table where constraint violation row information is written. The general form of such a table is table name , row id, and error message. So it's just a simple table that contains pointers to other tables whose rows violate constraints. Of course, this requires that you have access to an appropriate EXCEPTION table (such as that created by Oracle's UTLEXCPT.SQL script in the RDBMS admin directory). The Only Extract Object Names Like field lets you filter using simple Oracle pattern matches (for example, LIKE '%X%' ). And the Include Schema Name Prefix for Objects field lets you generate statements that look like SCHEMA.OBJECT. This is useful for two reasons. First, you can use a privileged schema like SYSTEM to run the generated script to create the objects in another schema. And second, you can use the Substitute This for Schema Name field to reverse-engineer from one schema into another.

Figure 6.40. TOAD Generate Schema Script ”Script Options.

The fourth step is the Storage Clause Options tab, which offers numerous and key capabilities related to Extents and Tablespaces, shown in Figures 6.41 and 6.42 respectively. These two tabs provide a horde of capabilities, so review the following very carefully .

Figure 6.41. TOAD Generate Schema Script ”Extents.

Figure 6.42. TOAD Generate Schema Script ”Tablespaces.

The Extents tab is a bit complicated as it provides options for you to define three distinct kinds of information. First, the top left portion of the screen offers check boxes for forcing and scaling certain storage parameter values. These options are fairly self-explanatory. The group box on the top right side of the screen permits you to define what is considered small, medium, large, and huge based on either object or extent size. These settings are referenced on both the bottom portion of this screen and on the Tablespaces tab shown in Figure 6.42. The group box on the bottom of the screen permits you to force the object extent sizes based on the object's relative size. For example, medium- sized objects (that is, those less than or equal to 5 megabytes in size) should use 512KB extents. This is a complicated tab. But if you spend the time to set all these options correctly, the reverse-engineered scripts you can generate will be awesome .

The Tablespaces tab permits you to relocate objects of various types from one tablespace to another. You can move them all or by their size. In Figure 6.42, the settings shown indicate to relocate small tables to tablespace USER_DICT, medium tables to tablespace QADB_DICT, large tables to tablespace QADB_LOCAL, and huge tables to tablespace DWDB_LOCAL. Note that you need to make these selections for each object type shown on the left side of the screen. Also remember, the meaning of small, medium, large, and huge was defined back on the Extents tab in Figure 6.41.

You're not quite finished yet, though. The ideal scenario would be to have these reverse-engineered DDL scripts waiting for you each morning in your scripts directory, already generated and ready for inspection. That way you could start your day by quickly checking with current scripts to rebuild your schemas if the need arose. You can schedule this report to run on your Windows machine via the Microsoft Task Scheduler as shown in Figure 6.43. For Windows 2000, this is located at StartSettingsControl PanelScheduled TasksAdd Scheduled Task.

Figure 6.43. Windows Scheduling of TOAD Generate Schema Script.

Note that you're simply scheduling Windows on your PC to run TOAD in command-line mode, where you provide the connection information and a generate schema script (GSS) command text file. To produce that file is very simple. You merely need to open the screen, make all your selections, and then choose the Save All 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).