Advantage Developer Zone


Creating Administrative SQL Scripts

Monday, April 06, 2009

In the past, to make changes to or use multiple elements in a Data Dictionary(DD) (Tables, Indexes, Users, etc), they needed to be changed manually through ARC, or run as a complex set of SQL statements from an end user application. Since the implementation of SQL Persistent Stored Modules(PSM) in Advantage 8.0, the same functionality is available through SQL scripting.

Why should I do this?

For anyone who does continued maintenance, upgrades, or new deployments of DDs, having a custom set of tools built into a DD will increase productivity. Reindexing all tables, changing settings on specific tables, or working with a specific set of indexes on a table, this can all be done through SQL scripting. Storing those custom scripts in stored procedures or even User Defined Functions(UDF) will allow them to be available at anytime without having to rewrite the script each time.

How do I setup these kinds of scripts?

Scripts that programmatically change multiple elements in a Data Dictionary have two key parts: The definition of the list of objects and the statement to run. The declaration of the list is very simple, as the following defines a list of all the tables in a DD:

DECLARE stmt String;
DECLARE tName String;
OPEN allTables AS SELECT * FROM system.tables; // Open cursor with all tables.

As shown above, the allTables variable is declared as a cursor. The cursor is then set as SELECT * FROM system.tables, which is all the tables in the current DD. The two string variables will be used later. Now that we have the list of tables, we can pack all of our tables in the next part of our script:

WHILE FETCH allTables DO // Go through tables.
//Change table property to set Auto Create to specified setting. 
tName = allTables.Name;
stmt = 'EXECUTE PROCEDURE sp_ModifyTableProperty(''' + tName + ''', ''TABLE_AUTO_CREATE'',;
CLOSE allTables;

Here we start with a WHILE FETCH on our cursor. This will allow us to go record by record through our cursor. To use each table, the table name is stored in a separate variable, which will then be used in the statement(s) that will be run. Each statement should be formulated into a string, and then executed. As above, the sp_ModifyTableProperty procedure is concatenated into a string, and then run by EXECUTE IMMEDIATE.

Other Notes

In the example given above, the complete column list from system.tables was used for the cursor, even though calling SELECT name FROM system.tables would have sufficed. The reason for this is that by having the full table available more complex scripts are able to be created. For example, the script could also run the sp_PackTable procedure on all DBF tables by adding an IF statement which uses allTables.Table_Type to determine the table type without making an additional call to system.tables. How the SELECT statement is formulated is directly dependent on what the script will need.

These kinds of scripts are not limited to only calling stored procedures. Any valid SQL statement will work and is recommended if that functionality is necessary. For example, if a script was necessary to remove any record with a user_id = #, that would be possible with the same method explained above.

Other recommended system tables to use would be columns, users, usergroups, indexes, views, etc. The complete list can be viewed by executing SELECT * FROM system.objects.


This kind of programmatic approach to actively working with multiple DD objects is a powerful tool that will increase the efficiency of your database applications. It will save your engineers time and diversify the set of tools available to you, allowing what originally had to be manually done or included in your application, to be performed automatically on the server.