Advantage Knowledgebase Item
Last Modified: Thursday, September 25, 2008 Product: Advantage Database Server ( General ) 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?
|