Reverse-engineer database

[Arthur Fuller]

I`d like to find a script that will create a "create" script for an entire database. It would write a create-table script for every table in the database, complete with all the PKs and FKs too.

Since I`m asking for the world (or at least the world`s creation script), then I`d also like the script to (optionally) write insert statements for all the rows currently residing in all the tables.

Do you have such a script? If not, can you give me a couple of pointers to assist me in writing my own?

TIA

[Levi Akers]

On 8/31/98 4:51:16 AM, Arthur Fuller wrote:
> I`d like to find a script that will create a "create" script for an entire
> database. It would write a create-table script for every table in the
> database, complete with all the PKs and FKs too.

Since I`m asking for
> the world (or at least the world`s creation script), then I`d also like the
> script to (optionally) write insert statements for all the rows currently
> residing in all the tables.

Do you have such a script? If not, can you
> give me a couple of pointers to assist me in writing my own?

TIA

Well the first part is fairly easy. The enteprise manager has a utility
that will create the scripts for just about any object. If you click on the
database you want to script then click on Object - Generate SQL Scirpts
from the menu. It has options on what objects you want created, tables,
stored procedures, logins etc. Click on what you want and then create
the script.

The second part for the data if you use BCP you can copy the data out
to a file and from a file back into a table. You can get specific syntax
from the help or SQL Books Online for BCP. This should get you started.

Levi

On 8/31/98 8:34:00 AM, Levi Akers wrote:
> On 8/31/98 4:51:16 AM, Arthur Fuller wrote:
> I`d like to find a script
> that will create a "create" script for an entire
> database. It would
> write a create-table script for every table in the
> database, complete
> with all the PKs and FKs too.

<-Snip->

TIA

Well the first part is fairly easy. The enteprise manager
> has a utility
that will create the scripts for just about any object. If
> you click on the
database you want to script then click on Object -
> Generate SQL Scirpts
from the menu.

<-Snip->

This will only allow you to do it online. If you are really chasing a script to generate the script you will have to use SQLOLE. I have posted an example into the Scripts area (should be available tomorow I guess) that generates all the index scripts to a specifed file. Use this as a start point and see the Books Online OLE section for further information.

>The second part for the data if you
> use BCP you can copy the data out
> to a file and from a file back into a
> table. You can get specific syntax
> from the help or SQL Books Online for
> BCP. This should get you started.

Levi


I agree that BCP is the only way to go if moving all the data. You may want to setup two scripts, one to create the tables and the other for the indexes to allow the use of fast BCP. (Remeber to turn the BulkCopy/SelectInto option on for the database as well. Consider turning the table level one on as well to prevent extent locks).

Keith

[Jasper]

On 8/31/98 4:51:16 AM, Arthur Fuller wrote:
> I`d like to find a script that will create a "create" script for an entire
> database.

I recommend starting with the following stored procedure.
sp_help_revdatabase
It is explained in detail in the Books Online.

Regards

Jasper