How can I save the schema of a SQL Database to a file?

Question

I'm writing a software application in C#.Net that connects to a SQL Server database. My C# project is under SVN version control, but I'd like to include my database schema in the SVN repository as well. An answer to a previous question of mine suggested storing the scripts to generate the database in version control. Is there a way to automatically generate these scripts from an existing database?

I'm very new to SQL Server, but I noticed in management studio that the SQL commands to create a table can be generated automatically by right clicking on the table and clicking "Script Table As". Is there an equivalent command that would work with the entire database?

Answer 1

You didn't mention which version of SQL Server, but in 2008 (and possibly earlier), you can right-click the database and choose Tasks -> Generate Scripts. The wizard will walk you through the steps.

Answer 2

I've had trouble with the built-in script generation. In particular, trying to recreate the database from the generated script has failed due to objects being scripted in the wrong order. (All the objects were there, it just took some re-ordering.)

There is a utility ExecuteSQLScript that dumps the types of objects you specify to script. It can even create a script per object, which is very useful for SCM, so you can easily track changes to individual objects.

http://exportsqlscript.codeplex.com/

Answer 3

You can use SQL-DMO or SQL SMO to script out, or a product like APEXSQLScript (if you have a lot of dependencies, you're going to want to use a mature 3rd party tool like this).

Answer 4

We use Red-gate compare which allows comparison (in pro version) against a folder, which is under SVN source control. The Apex tool mentioned probably has a similar feature.

Personally, I find the SSMS script generator a tad clumsy and prefer to pay for Red Gate stuff