Click here to Skip to main content
15,845,367 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Hello,
Currently I'm working on a program for database comparison. In this program I need to create scripts for databse objects. I found out that script generation is too slow but generating scripts using SSMS is pretty fast. In one thread I have found that SSMS uses SMO for script generation, but I couldn't find any suggestions for performance improvement.
Server srv = new Server(new ServerConnection("SERVER", "USERID", "PASSWORD"));
srv.SetDefaultInitFields(typeof(Table), true);
Database db = srv.Databases[database];
ScriptingOptions so = new ScriptingOptions();
so.AllowSystemObjects = false;
.
.
.
db.PrefetchObjects(typeof(Table),so);
.
.
.
foreach(Table tb in db.Tables)
{
  StringCollection sc = tb.Script();
  .
  .
  .
}

Interesting is that db.PrefetchObjects(typeof(Table),so) has almost no effect for script generatino of tables, but rapidly increases script generation of stored procedures.
Can someone explain to me, what kind of mechanism SSMS uses for script generation, or is it top secret information? :P


Regards
Robert
Posted
Updated 24-Apr-11 1:04am
v2
Comments
Ed Nutting 24-Apr-11 6:04am    
Improved for spelling/grammer a.k.a. readability.

OK. I found a solution:
Server srv = new Server(new ServerConnection("SERVER", "USERID", "PASSWORD"));
srv.SetDefaultInitFields(typeof(Table), true);
Database db = srv.Databases[database];
ScriptingOptions so = new ScriptingOptions();
so.AllowSystemObjects = false;
var dt = db.EnumObjects(DatabaseObjectTypes.Table);
var urns = new Microsoft.SqlServer.Management.Sdk.Sfc.Urn[dt.Rows.Count];
//get urns of tables to script
for (int rowIndex = 0; rowIndex < dt.Rows.Count; ++rowIndex)
{
 urns[rowIndex] = dt.Rows[rowIndex]["urn"].ToString();
}
//script tables
var scripter = new Scripter(srv);
var scripts = scripter.Script(urns);
foreach (var script in scripts)
{
 System.Diagnostics.Trace.WriteLine(script);
}

it is necessary to script all objects at once.
 
Share this answer
 
Comments
lemur 5-Aug-15 9:07am    
Its worth noting that you can if you don't have the Management SDK you can use an array of Microsoft.SqlServer.Management.Smo.Urn objects as well.
I find many workaround: After logging all script one by one for each SMO.Table, I discover that some script take less than 1 second, other take about 50 second!

So a first workaround is to work with multithreading.Doing so, make my scripting that took 2 hours to take know only 20 minutes 600% faster. I try with a basic ThreadPool, without optimize it in testing how many thread allow the best performance. I will say you it after. (I will test using different number of thread and logged it in order to see the number of thread in my computer that improve best performance)

Another workaround, is to use DBDiff in codeplex which source allow you to script faster without SMO but not usefull for those who want less code (script manually ;-). You have to use class to create sql script.But the tool is very good to synchronise database;-)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900