HostWeb Forums » Microsoft Databases » microsoft.public.sqlserver.programming » SMO question: Is this the right place? ScriptingOptions question
Topic: SMO question: Is this the right place? ScriptingOptions question
First, if there's a better newsgroup for SMO questions, please let me
know.
I'm trying to figure out SMO with SQL 2005 and Visual Studio 2005 (using
Visual Basic), and a few things are confusing to me.
In order to generate table create scripts, you can ...
1. Use the Scripting class in Microsoft.SqlServer.Management.Smo which
does scripting.
2. Even if you don't use this class, you can still use SMO and get a
table object in a database, and call TableObject.Script which returns
the script. Is one method preferred over the other?
3. You can also set the ConnectionContect.SQLExecutionModes to
SqlExecutionModes.CaptureSql and then do a TableObject.Create, instead
of TableObject.Script, and then look at the
ConnectionContext.CapturedSql.Text to get the script statements that
were created.
Having three ways to do this seems really confusing. Comments are
appreciated. For now, I'm going with TableObject.Script to capture the
table create scripts that I want.
HOWEVER, I want to set some of the scripting ooptions, since I don't
want the Use statements. SO, I discovered
ScriptOptions.IncludeDatabaseContext, but I can't figure out what object
it is part of, in order to set it to False.
Here's what I have so far:
Dim DatabaseConnection As New _
Microsoft.SqlServer.Management.Common.ServerConnection
DatabaseConnection.ServerInstance = "Machine\Instance"
DatabaseConnection.DatabaseName = "DatabaseName"
DatabaseConnection.LoginSecure = True ' Integrated Authentication
' Try to open the connection, give error on failure.
Try
DatabaseConnection.Connect()
Catch
[...]
End Try
So, I now have a database connection. (Setting the database name here
seems superfluous, but anyway...)
Then, I do this:
Dim SMODBConnection As New Microsoft.SqlServer.Management.Smo.Server _
(DatabaseConnection)
which creates a new SMO thing (which is apparently some local thing
created in hyperspace, or in another dimension) that uses the database
connection that I set up already.
From there, I am able to get a list of the tables in the database with
SMODBConnection.Databases(DatabaseName).Tables, and I can get the create
scripts for each table, and I am ultimately writing these create scripts
to files. (I could accomplish the same thing by clicking around in
SSMS, but I am setting up a program to generate a bunch of
Table/View/Procedure/Constraint create scripts in certain pre-designated
file folders as part of our local installation framework, and the
process needs to be automated and have fixed output paths, etc.)
Although I know that I want to set ScriptOptions.IncludeDatabaseContext
to False, I can't see how to actually do this (the property is read
only). I am missing some small piece of glue or linkage here. There's
ScriptOptions and ScriptingOptions, and I need an instance of SOMETHING
to set ScriptingOptions on; what am I missing?
The examples I have found on Google have not been helpful so far and VS
2005 has a link that claims to send you to an example, but it doesn't.
Thanks for any help.
David Walker
Replies below ↓