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?
LVL 17
ramromconsultant Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
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
ramromconsultant Author Commented:
I want to create a text file containing DDL (CREATE TABLE statements) for the tables in an Access database.
nico5038Commented:
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)
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

grayeCommented:
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
ramromconsultant Author Commented:
" export the schema as an XML"

How do I do this?
grayeCommented:
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.
ramromconsultant Author Commented:
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.
nico5038Commented:
A2003 will, but a re-engineering tool is the safest way.
Just checkout a trial version.

Nic;o)
ramromconsultant Author Commented:
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
PAQ_ManCommented:
Closed, 125 points refunded.
PAQ_Man
Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.