Advantage Developer Zone

 
 
 

Advantage Knowledgebase Item




Ref No:
080917-2080
Last Modified:
Thursday, September 25, 2008
Product:
Advantage Database Server ( General )
Category:
Programming
Title:
How to Change the Auto Create Property For All Tables in a Database
Problem Description:
There is no existing API to change the Auto Create property for all tables in a data dictionary.

Can a UDF (user defined function) be created to change the Auto Create property for all tables in a database?
Solution:
1.) Run the script below to create the UDF.
2.) Run the function using one of the two examples to either turn on or turn off the Auto Create functionality.

/*
Change all tables Auto Create property to the setting passed to the function.

Example for running the function:
SELECT autoCreate('True') FROM system.iota;
SELECT autoCreate('False') FROM system.iota;

*/

CREATE FUNCTION autoCreate
(
setting CHAR ( 10 )
)
RETURNS LOGICAL
BEGIN
DECLARE allTables CURSOR;

OPEN allTables AS SELECT * FROM system.tables; //Open cursor with all tables.

WHILE FETCH allTables DO //Go through tables.
TRY
//Change table property to set Auto Create to specified setting.
EXECUTE PROCEDURE sp_ModifyTableProperty(allTables.Name,'TABLE_AUTO_CREATE',setting,'NO_VALIDATE','savefail.adt');
CATCH ALL
CONTINUE;
END TRY;
END WHILE;
CLOSE allTables;
RETURN TRUE;


END;
Was This Item Helpful?