Tip #19 – Setting Table Properties via SQL

The standard SQL syntax includes a CREATE TABLE command for creating a new table. This statement can be used on free table and dictionary connections. When connected to a dictionary the table will be created in the dictionary if the user has the proper rights. If you want to create a free table on a dictionary connection use the AS FREE TABLE command at the end of your CREATE TABLE statement. You can also specify IN DATABASE to ensure that the table is created in the data dictionary.

The CREATE TABLE syntax includes the ability to add constraints for the new fields. However, it does not include syntax for creating other field and table properties available to dictionary bound tables. These include; validation expressions, validation messages, default indexes, auto create and encryption. To set these properties you must use system procedures.

sp_ModifyFieldProperty modifies a property for a specific field within the table. This system procedure can set any of the field constraints; minimum and maximum values, default value and null valid which can also be set with a CREATE TABLE statement. This system procedure also allows you to add/modify a field comment and validation message. These properties are not part of the CREATE TABLE syntax.

The following example sets the comment/description for the Address1 field in the Address table. In this case we do not need to specify a way to handle validation since this does not affect the table data. If a validation rule such as APPEND_FAIL is specified you must also define a Fail Table so records not meeting the new criteria can be preserved.

EXECUTE PROCEDURE sp_ModifyFieldProperty('Address', 'Address1', 'COMMENT', 'Street Address', 'NO_VALIDATE', NULL)

sp_ModifyTableProperty modifies a property for the specified table. These properties include; comment, validation expression, validation message, primary key, encryption, default index, permission level, auto create and memo block size. The syntax is very similar to sp_ModifyFieldProperty with the same mechanism for handling records which do not meet the new constraints.

The following example script encrypts all the tables in the dictionary. It first gets a list of tables using the system.tables view and then sets the TABLE_ENCRYPTION property. This is similar to setting the Auto Create Property for all tables which is outlined in a knowledge base article.

-- Script to Encrypt all Tables
DECLARE @cTables CURSOR AS SELECT * FROM system.tables;
OPEN @cTables;
WHILE FETCH @cTables DO
  TRY
    EXECUTE PROCEDURE sp_ModifyTableProperty(@cTables.Name,
     'TABLE_ENCRYPTION', 'TRUE', 'NO_VALIDATE', NULL);
  CATCH ADS_SCRIPT_EXCEPTION
    -- If table is already encrypted then continue to the next table
    IF __errcode = 5098 THEN
      CONTINUE;
    ELSE
      RAISE;
   END IF;
  END TRY;
END WHILE;
CLOSE @cTables;