Optimization using SMO - SQL Server Management Object - to script table.
With SMO, it is easier to programmatically make same tasks than SQL Server 2008 Management Studio wizard which allows script all database objects (tables,
stored procedure, index, data...):
The MSDN URL below will explain you how SMO works:
Using the Code
One trouble I have encountered in scripting with SMO tables (many hundred) was that scripting with SMO has taken 2 hours against only a few minutes with the SSMS wizard. One workaround was to firstly use
PrefectObject and secondly make the call script method with multithreading. It allows me to decrease time processing from 2 hours to 20 minutes.
PrefetchObject to make one call to load table collection:
Database db = ...
ScriptingOptions so = new ScriptingOptions();
You have also to call your script method using multithreading (threadpool or task ).
Points of Interest
Without making a
prefetchObject method call, your application will make a lot of round trip to SQL Server to make your script. The second point is that SMO is not written with multithreading so you have to do it on your own.