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?

devio · Accepted Answer · 2009-11-08T08:21:26.640

4

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)

edited Nov 08 '09 at 08:21

answered Nov 08 '09 at 08:16

devio

36,858
7
80
143

1

Does your code work fine for SQL Server 2008? I see that it requires libraries from SQL Server 2005 installed in order to work. Otherwise, it is a _wonderful_ tool. – cjbarth Sep 12 '11 at 19:59
1

I use version 0.19 regularly on 2008 (10.0.2531.0 SP1) – devio Sep 13 '11 at 05:41
Current version does not work for a SQL Azure database. Not too surprising seeing as it doesn't work from Generate Scripts inside SSMS 2008 R2 either, both ending with the same error message about Index was outside the bounds of the array. (see my question here: http://stackoverflow.com/questions/9622688/use-sqlmetal-on-sql-azure-database) – mellamokb Mar 08 '12 at 18:38
Valid for SSMS 2012, SSMS 2014 ? – Kiquenet Jan 31 '17 at 11:29
1

In the newer SQL Server Management Studio versions, the "Generate Scripts" wizard also has the ability to generate SQL code for both **Schema** and **Data**. Is this also possible with SMOscript? – Uwe Keim Nov 15 '17 at 12:52
3

Link in answer is broken. – Suresh Kamrushi Oct 11 '18 at 08:39

score 3 · Answer 2 · answered Nov 10 '09 at 00:20

3

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

answered Nov 10 '09 at 00:20

Mathias F

15,906
22
89
159

score 2 · Answer 3 · answered Nov 08 '09 at 08:16

2

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

answered Nov 08 '09 at 08:16

Kane

16,471
11
61
86

score 1 · Answer 4 · answered Nov 08 '09 at 08:18

1

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

answered Nov 08 '09 at 08:18

Konamiman

49,681
17
108
138

score 1 · Answer 5 · answered Nov 13 '17 at 19:37

1

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

answered Nov 13 '17 at 19:37

S. Rasmussen

163
1
6

That looks awesome! Thanks a lot . – Uwe Keim Nov 15 '17 at 13:32
This library is no longer maintained... Thanks Microsoft!.... – Tim Dec 02 '19 at 21:42

Programmatically generate script for all objects in a database

5 Answers5

Linked