Have Geek Brain

How to save SQL database schema to file?

I am writing a software application in C # .Net that connects to a SQL Server database. My C # project is under SVN, but I would like to include my database schema in the SVN repository. The answer to the previous question suggested keeping the scripts for generating the database in version control. Is there a way to automatically generate these scripts from an existing database?

I am very new to SQL Server, but I noticed in management studio that 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 will work for the entire database?

+3


a source to share


5 answers


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



+1


a source


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



+5


a source


We use Red-gate comparison, which allows comparing (in pro version) with a folder under control of the SVN source. Perhaps the mentioned Apex tool has a similar function.

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

+1


a source


I'm having trouble with the built-in script generator. In particular, the attempt to recreate the database from the generated script failed due to the objects being written in the wrong order. (All the objects were there, it just needed a reordering.)

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

http://exportsqlscript.codeplex.com/

0


a source


You can do this even without SQL Agent in SSMS.

Right click on the DB for which you want to create a script to export the schema for> Tasks> Create Scripts> "Advanced Options" in the 3rd screen> At the bottom of the "General Section" you have "Data Types for Script" with Data Only / Schema and Data Only / Schema .

enter image description here

0


a source






More articles:


All Articles