How can I automate the "generate scripts" task in SQL Server Management Studio 2008?

Question

I would like to automate the script generation in SQL Server Management Studio 2008.

Now I am doing:

• Right-click on my database, Tasks, "Generate Scripts ..."
• manually select all the export options that I need, and click select all on the "select object" tab
• Select export folder
• Finally click Finish

Is there a way to automate this task?

Edit: I want to generate the creation of scripts, and not modify the scripts.

Answer 1

What Brann of Visual Studio 2008 SP1 Team Suite says is version 1.4 of the Database Publishing Wizard. It is installed with SQL Server 2008 (perhaps only professional?) In \ Program Files \ Microsoft SQL Server \ 90 \ Tools \ Publishing \ 1.4. A VS call from the server explorer simply calls this. You can achieve the same functionality using the command line as:

sqlpubwiz help script 

I don’t know if v1.4 had the same problems as v1.1 (users are converted to roles, restrictions are not created in the correct order), but this is not a solution for me, because it doesn’t "script objects in different files, such as the Tasks-> Generate Scripts in SSMS option.I am currently using a modified version of Scriptio (uses the MS SMO API) to act as an improved replacement for the database publishing wizard (sqlpubwiz.exe). It is currently not available for scripting from the command line, I could add this contribution in the future.

Scriptio was originally posted on Bill Graziano's blog, but was subsequently released on CodePlex by Bill and updated by others. Read the discussion to learn how to compile it for use with SQL Server 2008.

http://scriptio.codeplex.com/

EDIT: From now on, I started using the RedGate SQL Compare product for this. This is a very good replacement for everything the publication sql wizard should have done. You select the database, backup or snapshot as the source, as well as the folder as the output location, and it completely resets the folder structure. This is the same format that their other product, SQL Source Control, uses.

Answer 2

SqlPubwiz has very limited capabilities compared to generating a script in SSMS. Unlike the options available with SMO , they almost exactly match the capabilities in SSMS, assuming that this is probably even the same code. (I would hope that MS did not write it twice!) There are several examples on MSDN, for example this one , that show script tables as separate objects. However, if you want everything to be correct script with a "full" scheme, which includes objects "DRI" (declarative referential integrity), such as foreign keys, then the script tables separately do not work correctly with the dependencies. I found that it is necessary to collect all the URNs and pass them to the script as an array. This code, modified from the example, works for me (although I suppose you could remove it and comment on it a bit):

 using Microsoft.SqlServer.Management.Smo; using Microsoft.SqlServer.Management.Sdk.Sfc; ... // Connect to the local, default instance of SQL Server. Server srv = new Server(); // Reference the database. Database db = srv.Databases["YOURDBHERE"]; Scripter scrp = new Scripter(srv); scrp.Options.ScriptDrops = false; scrp.Options.WithDependencies = true; scrp.Options.Indexes = true; // To include indexes scrp.Options.DriAllConstraints = true; // to include referential constraints in the script scrp.Options.Triggers = true; scrp.Options.FullTextIndexes = true; scrp.Options.NoCollation = false; scrp.Options.Bindings = true; scrp.Options.IncludeIfNotExists = false; scrp.Options.ScriptBatchTerminator = true; scrp.Options.ExtendedProperties = true; scrp.PrefetchObjects = true; // some sources suggest this may speed things up var urns = new List<Urn>(); // Iterate through the tables in database and script each one foreach (Table tb in db.Tables) { // check if the table is not a system table if (tb.IsSystemObject == false) { urns.Add(tb.Urn); } } // Iterate through the views in database and script each one. Display the script. foreach (View view in db.Views) { // check if the view is not a system object if (view.IsSystemObject == false) { urns.Add(view.Urn); } } // Iterate through the stored procedures in database and script each one. Display the script. foreach (StoredProcedure sp in db.StoredProcedures) { // check if the procedure is not a system object if (sp.IsSystemObject == false) { urns.Add(sp.Urn); } } StringBuilder builder = new StringBuilder(); System.Collections.Specialized.StringCollection sc = scrp.Script(urns.ToArray()); foreach (string st in sc) { // It seems each string is a sensible batch, and putting GO after it makes it work in tools like SSMS. // Wrapping each string in an 'exec' statement would work better if using SqlCommand to run the script. builder.AppendLine(st); builder.AppendLine("GO"); } return builder.ToString(); 

Answer 3

I wrote an open source command line utility called SchemaZen that does this. This is much faster than scripts from the management studio, and the output is more version friendly. It supports both schema and data scenarios.

To start the script run:

  schemazen.exe script --server localhost --database db --scriptDir c: \ somedir 

Then, to recreate the database from running scripts:

  schemazen.exe create --server localhost --database db --scriptDir c: \ somedir 

Answer 4

You can use the SQL Server Management Object (SMO) to automate SQL Server 2005 management tasks, including scripting: http://msdn.microsoft.com/en-us/library/ms162169.aspx .

Answer 5

If you are a developer, be sure to get involved with SMO. Here's a link to the Scripter class, which is your starting point:

Script class

Answer 6

I do not see powershell with SQLPSX mentioned in any of these answers ... I personally have not played with it, but it looks beautifully easy to use and is ideal for this type of automation task with tasks such as:

 Get-SqlDatabase -dbname test -sqlserver server | Get-SqlTable | Get-SqlScripter | Set-Content -Path C:\script.sql Get-SqlDatabase -dbname test -sqlserver server | Get-SqlStoredProcedure | Get-SqlScripter Get-SqlDatabase -dbname test -sqlserver server | Get-SqlView | Get-SqlScripter 

(ref: http://www.sqlservercentral.com/Forums/Topic1167710-1550-1.aspx#bm1168100 )

Project Page: http://sqlpsx.codeplex.com/

The main advantage of this approach is that it combines the configuration / customizability of using SMO directly, with the convenience and convenience of using a simple existing tool, such as the Database Publishing Wizard.

Answer 7

In "Tools"> "Options"> "Designers"> "Designers of tables and databases" there is an option "Automatically generate change scripts", which will generate one for each change that you make when you save it.

Answer 8

You can do this with T-SQL code using the INFORMATION_SCHEMA tables.

There are third-party tools - I like Apex SQL Script for what you say. I run it completely from the command line.

Answer 9

If you want to find a Microsoft solution, you can try: Microsoft SQL Server Database Publishing Wizard 1.1

http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

It creates a batch process that you can run anytime you need to rebuild scripts.

Answer 10

I use DB Comparer - its free and without fuss script the whole database and can compare with another database, and also produce a diff script. Great change scenarios to develop for production. http://www.dbcomparer.com/

Answer 11

There is also this simple command line tool that I build for my needs.
http://mycodepad.wordpress.com/2013/11/18/export-ms-sql-database-schema-with-c/

It can export all db and tries to export encrypted objects. Everything is stored in folders and separate sql files for easy file comparison.

Code is also available on github.

Answer 12

From Visual Studio 2008 SP1 TeamSuite:

On the tab "Server Explorer / Data Connection", a publishing tool for a provider will appear that performs the same actions as the "Microsoft SQL Server Database Publishing Wizard", but is compatible with MS Sql Server 2008.

Answer 13

I use VS 2012 (for a database on MSSQL Server 2008), database comparison has the ability to save it, comparison and parameters. In essence, these are your settings for delivery. After that you can update or generate a script.

I just find it a little awkward to load it from a file later (drag and drop from the Explorer window) since I cannot see the file in the solution explorer.

Answer 14

Try the new SQL Server command line tools for creating T-SQL scripts and monitoring dynamic management views.

Worked for me like a charm. This is a new Python-based tool from Microsoft that runs from the command line. Everything works as described on the Microsoft page (see link below)

You install it with pip:

$ pip install mssql-scripter

An overview of the command options as usual with h for reference:

mssql-scripter -h

Hint: if you log in to SQL-Server using Windows authentication, just leave your username and password.