Scripting database objects

Being able to script database objects is a powerful capability. There may be times when you'll need to have scripts handy, whether for versioning purposes or simply to provide them to your developers.

SMO largely drives this scripting capability. There is a class called Microsoft.SqlServer.Management.Smo.Scripter, which can be passed a collection of SMO objects to script:

$script.Script($smoObjects)

For example, if you need to script all stored procedures, you can add all the stored procedures into an array and pass this array to the Scripter object. You need to ensure that you are passing the actual stored procedure object and not just the names.

The Scripter object also accepts ScriptingOptions. There are a number of options that can be set. They include the following options:

• DriAll

• DriIndexes

• DriNonClustered

• DriPrimaryKey

• DriUniqueKeys

• Encoding

• SchemaQualify

• ScriptDrops

• ScriptOwner

Note

The complete documentation for all properties that can be set using the ScriptingOptions...