With SQL Server 2000, I use scptxfr.exe utility to generate DB creation script, but it has some errors with dependencies - when view 1 has subquery where another view 2 is used then when generating creation script for DB I get script for creation of view 1 before than view 2 and it executes with errors.
We have moved to SQL 2005 and now we are using scptxfr.exe with the same problems. I've tried to use SMO for this but it seems that it has the same problems.
Can you show me the right way to go to get the correct script for DB
Thanks.
Getting creation script for database (including all objects)
pjencyjoy
Have you tried passing in a scripting options object with dependencies on to the script function
For example:
Server svr = new Server("your server name");
Database db = svr.Databases["The database name"];
View vw = db.Views["the View name"];
ScriptingOptions so = new ScriptingOptions();
so.WithDependencies = true;
String[] theScript = vw.Script(so);
You'll obviously have to walk through and script out each of the objects to get a full script of the database but the DBs and Objects can be indexed by number as well as name, so you can just throw them each into loops.