Tuesday, November 25, 2008

Tip #17 – Set Database Properties via SQL

Data dictionary properties can be set in a variety of ways. Advantage Data Architect provides a graphical interface for changing these properties, however, sometimes they need to be changed programmatically. This can be done using an API, AdsDDSetDatabaseProperty or using a system procedure.

The sp_ModifyDatabase system procedure sets one database property at a time. To use the system procedure you specify the property using its keyword (i.e. VERSION_MAJOR) and then specify the value (i.e. 2). The example script below changes the database version and adds the date of the update to the comments field.

-- Script to UPDATE the database properties
DECLARE @sComment STRING;
-- Get the current comment
@sComment = (SELECT [Comment] FROM system.dictionary);
-- Add the upgrade info to the comment
@sComment = @sComment + '-- Upgraded to version 2.0 ';
-- Add the current timestamp to the comment
@sComment = @sComment + CONVERT(NOW(), SQL_CHAR);
-- Update the dictionary properties
EXECUTE PROCEDURE sp_ModifyDatabase('VERSION_MAJOR', '2');
EXECUTE PROCEDURE sp_ModifyDatabase('VERSION_MINOR', '0');
EXECUTE PROCEDURE sp_ModifyDatabase('COMMENT', @sComment);

No comments: