Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 3 of 3 total |
Changes to multiple table structures using EDB Configuration Tables and TableColumns tables |
Thu, May 19 2011 6:05 PM | Permanent Link |
Adam Brett Orixa Systems | 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? |
Thu, May 19 2011 6:34 PM | Permanent Link |
Adam Brett Orixa Systems | 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 |
Fri, May 20 2011 2:17 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
Wonderful isn't it - a whole new language to learn Roy Lambert |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |