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;