I'm currently evaluating ElevateDB to see if it fits my project and I was wondering how to get the "CREATE TABLE" statement from an existing table in ElevateDB Manager?

I created a table and filled some data and wanted to get the SQL script that will recreate that table with it's data (if possible).

<<I'm currently evaluating ElevateDB to see if it fits my project and I was wondering how to get the "CREATE TABLE" statement from an existing table in ElevateDB Manager?

I created a table and filled some data and wanted to get the SQL script that will recreate that table with it's data (if possible).>>

AQC,

From EDBMgr, select Explorer > SQL to display the SQL window. (Double) Click on the table in the navigator tree (or any other object) and the SQL window will show the Create Table ... statement for the table.

or

select createsql from information.tables where name='MyTable';

To export it with data, you need to reverse engineer the database by right clicking on the database node and selecting "Create" and on the output tab select "generate as script" and "Include Rows" and "Open in new SQL Window". This will generate an SQL script to build the database with data. There is no option that I know of to export a script just for one table.

Normally to backup the database you would create a Store (backup location) and right click on the database and select "Backup Database .." and select the table(s) to backup along with the Catalog (recommended) and this writes the database to the Store location in a binary format.

Barry

Barry

>To export it with data, you need to reverse engineer the database by right clicking on the database node and selecting "Create" and on the output tab select "generate as script" and "Include Rows" and "Open in new SQL Window". This will generate an SQL script to build the database with data. There is no option that I know of to export a script just for one table.

There is. Just open a new sql window, grab the table from the tree view on the left hand side and drag'n'drop into the sql window. You will be prompted if you want to include code to create the rows.

Roy

Roy,

Sweet!

Barry

Barry

Also note that you can do this:

SELECT CreateSQL FROM Information.Tables
WHERE Name = 'TableName'

This will return the CreateSQL for a table as a query-dataset.

You can use this in the context of a SCRIPT with a CURSOR in interesting ways, you can FETCH the CreateSQL and then call it to create the table, perhaps in another database.

If you are starting with EDB I would strongly suggest running the following statements:

SELECT * FROM Information.Tables
SELECT * FROM Information.TableColumns

Both with your Database and your Session selected in EDBMgr. Running these statements will show new sets of system information which are available in EDB.

Data from the tables shown by running "SELECT * FROM Information.Tables" on your Session can be returned by calling:

"SELECT * FROM Configuration.[TableName]"

For example:

SELECT * FROM Configuration.Databases

Some time spent looking at these tables will improve your understanding of EDB a lot.

The Information.TableColumns system table is amazingly useful, as it can be used to query and compare versions of databases. There is even an EDB SQL Keyword for this:

COMPARE DATABASE <SourceDatabaseName>
TO <TargetDatabaseName>

Hi,
toying around a little with CreateSQL, I noticed that if you have a Boolean field with DEFAULT FALSE, the SQL code in CreateSQL is something like this:

"LISTINT" BOOLEAN DEFAULT Fal,
"LISTDIN" BOOLEAN DEFAULT Fal,
"SCONTI" BOOLEAN DEFAULT Fal,

where the Fal instead than False causes errors when you try to use the SQL code.

Anyone else confirms or is it a problem of mine ?

TIA
Alex

Hi Alex,

> Anyone else confirms or is it a problem of mine ?

I can confirm this.   Using 2.16 against a unicode database created with
2.14.  It's dropping the last 2 characters of the default value, field
descriptions, and some of the field types.  Regarding the default value, if
I manually update the field from EDB Manager and reset the default to False,
it appears to save that down correctly.

I'll report this to Tim.


---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.1000years.com
http://www.atrex.com
---------------------------------------