I'd like to automate the script generation in SQL Server Management Studio 2008.
Right now what I do is :
Is there a way to automate this task?
Edit : I want to generate creation scripts, not change scripts.
You can use SQL Server Management Object (SMO) to automate SQL Server 2005 management tasks including generating scripts: http://msdn.microsoft.com/en-us/library/ms162169.aspx.
If you're a developer, definitely go with SMO. Here's a link to the Scripter class, which is your starting point:
In Tools > Options > Designers > Table and Database Designers there's an option for 'Auto generate change scripts' that will generate one for every change you make at the time you save it.
You can do it with T-SQL code using the INFORMATION_SCHEMA tables.
There are also third-party tools - I like Apex SQL Script for precisely the use you are talking about. I run it completely from the command-line.
If you want to a Microsoft solution you can try: Microsoft SQL Server Database Publishing Wizard 1.1
It create a batch process you can run anytime you need to rebuild the scripts.
Try new SQL Server command line tools to generate T-SQL scripts and monitor Dynamic Management Views.
Worked for me like charm. It is a new python based tool from Microsoft that runs from command line. Everything works like described on the Microsoft page (see link below) Worked for me with SQL 2012 server.
You install it with pip:
$pip install mssql-scripter
Command parameter overview as usual with h for help:
mssql-scripter -h
Hint: If you log in to SQL-Server via Windows authentication, just leave away Username and password.
From Visual Studio 2008 SP1 TeamSuite :
In the Server Explorer / Data Connections tab, there's a publish to provider tool which does the same as "Microsoft SQL Server Database Publishing Wizard", but which is compatible with MS Sql Server 2008.
There is also this simple command line tool I build for my needs.
http://mycodepad.wordpress.com/2013/11/18/export-ms-sql-database-schema-with-c/
It can export an entire db, and it tries to export encrypted objects. Everything is stored in folders and separate sql files for easy file comparison.
Code is also available on github.
