Programmatically generate script for all objects in a database

Question

For an automated setup build that generates the setup for an application which uses Microsoft SQL Server, I am currently evaluating whether the following is possible:

I want to programmatically (CMD script or C# code) execute the function "Generate Scripts" on a database that is accessible from Microsoft SQL Server Management Studio 2008.

I.e. call some code and have all the objects (tables, SPs, constraints, etc.) of a specified database as an SQL script.

Currently, I only need the structure, not the data.

Question: Is it possible to achieve this goal?

Solution 1

I wrote SMOscript, a command line tool to script all objects into a single file, or one file for all objects.

The Generate Scripts function of MSSQL typically does not consider references and dependencies to determine the sequence of objects (at least that was the case with 2000 and 2005)

Solution 2

I used http://www.codeplex.com/ScriptDB with great success.

Solution 3

You should be able to do it in managed code using SMO

Solution 4

You could invoke a standard select clause (using ADO.NET for example) over the sysobjects view.

Solution 5

Microsoft has also released a tool called 'mssql-scripter'. It can be found here: mssql-scripter

View more solutions