How to use SMO.Scripter to generate a "full-script" of DB?
- by ssg
What I'm trying to do is a very simple task; I'd like to create a script to generate a database along with tables, SPs and UDFs. This is done with a couple of clicks on SSMS interface.
However db.Script() only scripts CREATE DATABASE. Ok, so I iterate over objects one by one and script them individually. Now, what I have is an arbitrary order of CREATEs naturally failing during execution because dependent objects aren't created first.
Ok so I set WithDependencies flag so dependent objects ARE scripted first. However this causes redundant CREATE scripts for objects that are already created, and causes around 20x growth in SQL file and generation time. Not to mention the errors hit during execution.
I don't know if there is a way to mark objects "already walked in dependency tree", it doesn't seem likely. I might be missing a bigger picture somewhere, but MSDN recommends "Scripter" to generate scripts like the one I want.
I had used Transfer class before to transfer table definitions but it fails to create a failsafe script. It doesn't make sense to use a Transfer object to generate a script anyway. I want to do this the way it should be done, and without losing my faith in SMO.
