Link to home
Start Free TrialLog in
Avatar of ramrom
ramromFlag for United States of America

asked on

How to export a database as a series of CREATE TABLE SQL statements?

How do I  export a database as a series of CREATE TABLE SQL statements?
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Hi ramrom,

Some followups:

1) Are you looking to export tables only, or also query defs, forms, reports, and macros?

2) All tables (Queries, forms, reports, macros), or just certain ones?  If just certain ones, are they identified
by a rule (e.g., "everything starting with 'foo'"), or is it an arbitrary list?

3) Are you exporting to another Access MDB, or to a different kind of db (in which case, you can only export
the tables)?

Regards,

Patrick
Avatar of ramrom

ASKER

I want to create a text file containing DDL (CREATE TABLE statements) for the tables in an Access database.
Access can't generate the DDL, but you can use a re-engineering tool like powerdesigner (http://www.sybase.com/products/enterprisemodeling/powerdesigner) to get it.

Nic;o)
But you can export the schema as an XML (XSD file).... then the recieving application/database can import the schema.

It's not using DDL, but accomplishes the same thing
Avatar of ramrom

ASKER

" export the schema as an XML"

How do I do this?
Programmatically? or by the application?

From the application itself, you merely select a table (or two) and select export.... the select XML.   A popup will ask if you want to export the schema  alone or schema with data.
Avatar of ramrom

ASKER

My installation of Access 2000 does not offer xml amongst the "Save as type:" selections. Is that in a later version or does it require special installation? I don't find any reference to xml in help either.
A2003 will, but a re-engineering tool is the safest way.
Just checkout a trial version.

Nic;o)
Avatar of ramrom

ASKER

I solved the problem for the nonce by running the documentor wizard, saving its report to a text file, then parsing that with a pyhton program that generates the sql. So I will paq this question. Python code posted at:

http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/465757
ASKER CERTIFIED SOLUTION
Avatar of PAQ_Man
PAQ_Man
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial