Is there some way for me to generate SQL Scripts from an already existing database?

Question

Say I already created my database but forgot to save the sql commands do create it.

How could I reverse engineer the code from an already existing database?

I'm using Microsoft SQL Server Express 2008.

Answer 1

Your RDBMS comes with some sort of "dump" tool that will give you the structure and content of your database, in the form of SQL statements.

Answer 2

You can do this pretty easily by using SQL Server Management Studio (SSMS) - it's available for free if you don't already have it installed.

• Connect to the database
• Expand out Databases > YourDataBaseName.
• Right-click on the database and select the option "Script database as" then "Create To" then finally "File".

That will create the necessary scripts to recreate your database.

To script out all the tables in your database:

• Right-click on the database node
• Select "Tasks" then "Generate Scripts".
• When the wizard appears, click Next.
• Select the database. At this point you can check the "Script all objects in the selected database" which does exactly what it says, or if you leave it unchecked you will get the option later in the process to pick which items are scripted.
• Click next. Now you're given some scripting options.
• I'd suggest scrolling down the list and checking the option to Script Indexes/Script Triggers. You can also script the data if necessary (though I wouldn't do this if you've got a lot of data in your database).
• Modify any options you'd like and click Next.
• Select the database types you'd like to script (Users/Tables/Views). Click Next.
• Now you've got the opportunity to select more specific items. Hit Next and repeat the process of any of your other database types.
• Hit next one more time, then select where you'd like the script written to. You get the chance to review your selections.
• Click Finish.

Here's a link for the 2008 version SSMS Express 2008

Answer 3

As others have mentioned, if you have SQL Management Studio (you should, it's free as part of SQL Server Express). Fire it up, connect to your instance then expand the Database tree.

Right click on your database and select Tasks->Generate Scripts.. Click next, then Next again (which selects all objects in the database by default), pick an output option (defaults as "Save to File"), click next and voila!

If you also want to script the data as well as the schema, in the "Set Scripting Options" window, click on the Advanced button, scroll down to "Types of data to script" (just above the Table/View Options header) and select "schema and data".

[Edit] Tested - The Generate Scripts option exists and works in the (free) 2008 R2 edition of SSMS. See the link in my comment below for the URI for the R2 version.