I have a database where most tables have a "DateCreated" column of type Date which defaults to "Current_Date"

I would like all these tables to have a DateCreated field of Type TimeStamp which defaults to "Current_Timestamp"

The TableColumns Table in EDB says it is a "read only cursor" so the following script:

UPDATE INFORMATION.TableColumns
SET "DefaultExpr" = 'Current_Timestamp',
"Type" = 'Timestamp'

WHERE Name = 'DateCreated'

fails to run ... which is fair enough, I guess.

However I have about 200 tables, so manually writing the code is arduous ... I guess the only way to achieve this is with a SCRIPT which uses values drawn from a result-set based on the TableColumns table to populate a SQL Statement & execute it?? ...

Or can I interact with the tablecolumns table more directly?

Sorry, figured it out ... I am starting to get this:

SCRIPT
BEGIN                                                     
  DECLARE Tablename VARCHAR;
  DECLARE TCCursor CURSOR WITH RETURN FOR Stmt;
  PREPARE Stmt FROM 'SELECT * FROM Information.TableColumns WHERE Name=''DateCreated''';


  OPEN TCCursor;
  FETCH FIRST FROM TCCursor ('TableName') INTO TableName;
  WHILE NOT EOF(TCCursor) DO
     BEGIN
       EXECUTE IMMEDIATE
         'ALTER TABLE "'+TableName+'" ALTER COLUMN DateCreated AS TimeStamp DEFAULT Current_TimeStamp ';
     FETCH NEXT FROM TCCursor ('TableName') INTO TableName;
     END;
  END WHILE;
END

Adam


Wonderful isn't it - a whole new language to learn

Roy Lambert