Click here to Skip to main content
14,033,409 members
Click here to Skip to main content
Add your own
alternative version


2 bookmarked
Posted 7 Oct 2012
Licenced CPOL

Optimization using SMO because of latency - SQL Server Management Object - To script table

, 7 Oct 2012
Rate this:
Please Sign up or sign in to vote.
Workaround for SMO latency when scripting.


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.

Use PrefetchObject to make one call to load table collection:

Database db = ... // get your database root
ScriptingOptions so = new ScriptingOptions();
db.PrefetchObject(typeof(Table), so);

You have also to call your script method using multithreading (threadpool or task Wink).

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.


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


About the Author

Hassan Boutougha
Web Developer
United States United States
No Biography provided

You may also be interested in...

Comments and Discussions

QuestionWhen Scriptingthousand tables, this method does not seem excellent. Pin
Hua Yujun3-Apr-13 0:28
memberHua Yujun3-Apr-13 0:28 
GeneralMy vote of 1 Pin
cjb1107-Oct-12 21:31
membercjb1107-Oct-12 21:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web05 | 2.8.190423.1 | Last Updated 7 Oct 2012
Article Copyright 2012 by Hassan Boutougha
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid