SQL Script Generation

The issue

When using the script generator from SQL management Studio on a legacy Database, I m getting either errors when generating the scripts or a script I cant rerun to recreate the DB.

Quick Solution

There is the possibility to generate SQL scripts using the Sql Management Objects dll provided with the server.

Look for a dll with Smo in it like the following :

1
2
3

22/01/2020  00:30           125.344 Microsoft.SqlServer.Management.SmoMetadataProvider.dll
22/01/2020  00:30         4.271.016 Microsoft.SqlServer.Smo.dll
22/01/2020  00:30           194.976 Microsoft.SqlServer.SmoExtended.dll

If there is nosuch dll, there must be a package available either in the installation CD or on Microsoft website. Such DLL exists for other products, like Biztalk and they can be used to access or control the application.

In Visual Studio, reference the DLL and the name space in a project. Here is how I have extracted the scripts I needed :

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207

using Microsoft.SQLserver.management.smo ;
using Microsoft.SQLserver.management.sdk.sfc;
using System.IO;

namespace Tools
{
public class DbScripter 
{


private string _database;
private Server _srv = new Server();
private Database _db;

public  DbScripter ():this("DbName")
{
}

public  DbScripter (string database)
{
this._database = database;
if (this._srv.Databases.Contains(this._database))
this._dn = this._srv.Databases[this._database];
else
throw new exception ("Unknown database");
}

public void Generatescript (StreamWriter sw)
{
ScriptDatabase(sw)
ScriptDatabasetypes (sw)
scriptdatabasetables (sw)
scriptdatabaseviews (sw)
scriptdatabasestoredprocedure(sw)
}


ScriptDatabase (StreamWriter sw)
{
Scripter scrp = new Scripter (this._srv);
scrp.Options.ScriptDrops = true;
scrp.Options.IncludeIfNotExists = true;
scrp.Options.WithDependencies = false;

System.Collections.Specialized.StringCollection sc = scrp.Script (new Urn[] {this._db.Urn});
scrp.Options.SchemaQualify = true;

foreach (String st in sc)
{
sw,Writeline(st)
}

sw.Writeline("--");
scrp.Options.ScriptDrops = false;
scrp.Options.WithDependencies = false;

 sc = scrp.Script (new Urn[] {this._db.Urn});
 
 foreach (String st in sc)
{
sw,Writeline(st)
}

sw.Writeline("--");

}


ScriptDatabaseTables (StreamWriter sw)
{
	Scripter scrp = new Scripter(this._srv);
	scrp.Options.ScriptDrops = false;
	scrp.Options.AllowSystemObjects = false;
	scrp.Options.WithDependencies = false;
	scrp.Options.Indexes = false;
	scrp.Options.DriAllConstraints = false;
	scrp.Options.DriChecks = true;
	scrp.Options.DriDefaults = true;
	scrp.Options.DriAllKeys = false;
	scrp.Options.DriIndexes = true;
	scrp.Options.DriClustered = true;
	scrp.Options.SchemaQualify = true;
	
	// TAbles
	foreach (table tb in this._db.Tables)
	{
	if(tb.IsSystemObject == false)
	{
	sw.Writeline ("-- Scripting for table " + tb.Name );
	
	System.Collections.Specialized.StringCollection sc = scrp.Script(new Urn[] {tb.Urn});
	foreach(string st in sc)
	{
	sw.Writeline(st);
	}
	sw.Writeline("--");
	
	}
	}

	//Constraints
	scrp.Options.DriAllConstraints = false;
	foreach (table tb in this._db.Tables)
	{
		if(tb.IsSystemObject == false && tb.ForeignKeys.Count > 0)
		{
		sw.Writeline ("-- Scripting Keys for table " + tb.Name );
		
		foreach (ForeignKey fk in tb.ForeignKeys)
		{
			System.Collections.Specialized.StringCollection sc = scrp.Script(new Urn[] {fk.Urn});
			foreach(string st in sc)
			{
			sw.Writeline(st);
			}
			
		}
		
		sw.Writeline("--");
		
		}	
	}

}

ScriptDatabaseTypes (StreamWriter sw)
{
	Scripter scrp = new Scripter(this._srv);
	scrp.Options.ScriptDrops = false;
	scrp.Options.AllowSystemObjects = false;
	scrp.Options.WithDependencies = true;
	scrp.Options.Indexes = true;
	scrp.Options.DriAllConstraints = true;

	foreach (UserDefineDataType uddt in this._db.UserDefinedDataTypes)
	{
		sw.Writeline ("-- Scripting Type " + uddt.Name );
		System.Collections.Specialized.StringCollection sc = scrp.Script(new Urn[] {uddt.Urn});
		
		foreach(string st in sc)
			{
			sw.Writeline(st);
			}
			
		sw.Writeline("--");
	}
	
}

ScriptDatabaseViews (StreamWriter sw)
{
	Scripter scrp = new Scripter(this._srv);
	scrp.Options.ScriptDrops = false;
	scrp.Options.AllowSystemObjects = false;
	scrp.Options.WithDependencies = true;
	scrp.Options.Indexes = true;

	foreach (View view in this._db.Views)
	{
		if (view.IsSystemObject == false)
		{
		sw.Writeline ("-- Scripting View " + view.Name );
		System.Collections.Specialized.StringCollection sc = scrp.Script(new Urn[] {view.Urn});
		
		foreach(string st in sc)
			{
			sw.Writeline(st);
			sw.Writeline("GO");
			}
		}	
		sw.Writeline("--");
	}
	
}

ScriptDatabaseStoredProcedure (StreamWriter sw)
{
	Scripter scrp = new Scripter(this._srv);
	scrp.Options.ScriptDrops = false;
	scrp.Options.AllowSystemObjects = false;
	scrp.Options.WithDependencies = true;
	scrp.Options.Indexes = true;
	scro.Options.DriAllConstraints = true;

	foreach (StoredProcedure sp in this._db.StoredProcedures)
	{
		if (sp.IsSystemObject == false)
		{
		sw.Writeline ("-- Scripting Stored Procedure " + sp.Name );
		System.Collections.Specialized.StringCollection sc = scrp.Script(new Urn[] {sp.Urn});
		
		foreach(string st in sc)
			{
			sw.Writeline(st);
			sw.Writeline("GO");
			}
		}	
		sw.Writeline("--");
	}
	
}

}


### References :
* [MSDN SQL Server SMO](https://learn.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/installing-smo?view=sql-server-2016)