Automation Command Reference for Database Features of Visual Studio

Article
02/04/2013

You can use Visual Studio Design Time Extensibility (DTE) to perform certain operations in the Visual Studio Command Window. For example, you might do this if you want to import multiple scripts into your database project.

In this release of Visual Studio Premium or Visual Studio Ultimate, you can:

Import database or server objects and settings into a database project
Import objects and settings from a script
Compare database schemas
Compare database data
Perform refactoring operations
Work with partial projects
Work with objects in Schema View
Analyze Database Code

Quick Reference to Database Automation Commands

The following table contains a quick reference for all DTE commands for the database features of Visual Studio:

Area	Command
Data Compare	Data.NewDataComparison Data.DataCompareExportToEditor Data.DataCompareExportToFile Data.DataCompareFilterAllRecords Data.DataCompareFilterDifferentRecords Data.DataCompareFilterIdenticalRecords Data.DataCompareFilterOnlyInSourceRecords Data.DataCompareFilterOnlyInTargetRecords Data.DataCompareFilterSelectedRecords Data.DataCompareRefresh Data.DataCompareSelectSrcTgtDatabases Data.DataCompareShowUpdateScript Data.DataCompareStop Data.DataCompareWriteUpdates Data.DataDataCompareRefreshScriptPreview
Data Generator	Data.DataGeneratorExcludeAllColumns Data.DataGeneratorExcludeAllTables Data.DataGeneratorIncludeAllColumns Data.DataGeneratorIncludeAllTables Data.DataGeneratorPlanRefresh Data.DataGeneratorPopulate Data.DataGeneratorPreview
Import	Project.ImportDatabaseSchema Project.ImportScript
Partial Projects	Project.VSDBExportAsPartialProject Project.VSDBImportPartialProject Project.VSDBRemovePartialProject
Refactoring	Data.RenameRefactor Data.FullyQualifyNameRefactor Data.MoveSchemaRefactor Data.RenameServerDatabaseReferenceRefactor Data.WildcardExpansionRefactor
Schema Compare	Data.NewSchemaComparison Data.SchemaCompareExportToEditor Data.SchemaCompareExportToFile Data.SchemaCompareFilterAllObjects Data.SchemaCompareFilterDifferentObjects Data.SchemaCompareFilterEqualObjects Data.SchemaCompareFilterMissingObjects Data.SchemaCompareFilterNewObjects Data.SchemaCompareFilterNonSkipObjects Data.SchemaCompareFilterSkipObjects Data.SchemaCompareNextChange Data.SchemaCompareObjectDefnHorizontal Data.SchemaCompareObjectDefnOff Data.SchemaCompareObjectDefnVertical Data.SchemaCompareOptions Data.SchemaComparePreviousChange Data.SchemaCompareRefresh Data.SchemaCompareRefreshScriptPreview Data.SchemaCompareRestoreUpdateActionDefaults Data.SchemaCompareRestoreUpdateSelectionDefaults Data.SchemaCompareSelectSourceTargetSchemas Data.SchemaCompareShowUpdateScript Data.SchemaCompareSpecifySQLCMDVariables Data.SchemaCompareStop Data.SchemaCompareWriteUpdates
Schema View	Data.SchemaViewShowDependencies Data.ShowBuiltInElementsInSchemaView Data.ShowExternalElementsInSchemaView Data.DeleteObjectSchemaView Data.SynchronizeSchemaView Data.ToggleSchemaViewBySchema
Transact-SQL editor	Data.SqlEditorCancelQueryExecution Data.SqlEditorConnect Data.SqlEditorDisconnect Data.SqlEditorDisplayEstimatedExecutionPlan Data.SqlEditorEditSqlCmdFile Data.SqlEditorExecuteSql Data.SqlEditorIncludeActualExecutionPlan Data.SqlEditorIncludeStatistics Data.SqlEditorNewQueryConnection Data.SqlEditorQueryOptions Data.SqlEditorResetClientStatistics Data.SqlEditorResultsAsFile Data.SqlEditorResultsAsGrid Data.SqlEditorResultsAsText Data.SqlEditorSqlCmdMode Data.SqlEditorToggleResultsPane Data.SqlEditorValidateSqlSyntax
Database Code Analysis	Data.StaticCodeAnalysisConfigure Data.StaticCodeAnalysisRun

Importing Database or Server Objects and Settings

You can use the Data.ImportDatabaseSchema command from the Visual Studio Command Window to import objects and settings from a database or server. You highlight a database or server project in Solution Explorer before you use the Data.ImportDatabaseSchema command. For more information, see How to: Import Database Objects and Settings or How to: Import Server Objects and Settings.

You can specify the following parameters for the Data.ImportDatabaseSchema command:

Parameter	Required	Notes
/ConnectionString "YourString"	Yes	Specify the connection string to the database from which you want to import objects and settings. To import server objects, specify the "master" database.
/AlwaysScriptColumnCollation	No	Specify this option to always explicitly script column collations. If you do not specify this option, column collations are explicitly specified for only those cases in which the column collation does not match the database collation.
/IgnoreExtendedProperties	No	Specify this option if you do not want to import extended properties on the source database and its contents.
/IgnoreFileSizes	No	Specify this option if you do not want to import the sizes for log files and filegroups.
/OverrideDBConfiguration	No	Specify this option if you want to update the settings for the database project to match the settings for the source database.
/Overwrite	No	This option is ignored.
/DirectoryLimit N	No	Specify this option to reduce the time that is required to open and work with your database project by limiting the number of database objects that are stored in each folder in your Data. Replace N with the number of files that you want to allow in a single directory on disk.
/IgnorePermissions	No	Specify this option if you do not want to import permissions from the source database. Note By default, permissions are ignored by when you import objects and settings by using the wizard. You might want to specify this option to improve performance when you import large databases.
/AddImportedPermissionsToModel	No	Specify this option if you did not specify /IgnorePermissions and if you want to add permissions to the model of the database. If you add permissions to the model, the database project will load more slowly.

Importing Objects and Settings from a Script

You can use the Data.ImportScript command from the Visual Studio Command Window to import object definitions from a script. You highlight a database or server project in Solution Explorer before you use the Data.ImportScript command. For more information, see How to: Import Database Objects from a Script.

You can specify the following parameters for the Data.ImportScript command:

Parameter	Required	Notes
/FileName MyFile.sql	Yes	Specify the name of the script file that you want to import. If you want to import a file name that contains spaces, you must enclose the file name within quotation marks (for example, "My Script.sql").
/Encoding {Unicode \| UTF32 \| UTF8 \| UTF7}	No	Specify the encoding in which the file was saved. If you do not specify an encoding, the file is assumed to be UTF8.
/Overwrite	No	Specify this option if you want to overwrite object definitions that already occur in the database Data. If you do not specify this option, objects that already exist will not be imported.
/IgnoreExtendedProperties	No	Specify this option if you do not want to import extended properties on the source database and its contents.
/IgnorePermissions	No	Specify this option if you do not want to import permissions from the source database. Note By default, permissions are ignored when you import a script by using the wizard. You might want to specify this option to improve performance when you import large scripts.
/AddImportedPermissionsToModel	No	Specify this option if you did not specify /IgnorePermissions and if you want to add permissions to the model of the database. If you add permissions to the model, the database project will load more slowly.

Comparing Database Schemas

You can use the Data.NewSchemaComparison command from the Visual Studio Command Window to compare two database schemas. You specify a source schema provider and a target schema provider. For each provider, you specify the type and the identification for that provider. For more information, see How to: Compare Database Schemas.

You can specify the following parameters for each provider for the Data.NewSchemaComparison and Data.SchemaCompareSelectSourceTargetSchemas commands:

Parameter	Notes
/ProviderType ConnectionBased /ConnectionString "YourString"	Specify this syntax if you want to specify a connection string for a database as one of the schemas.
/ProviderType ConnectionBased /ConnectionName ConnectionName	Specify this syntax if you want to specify the name of a connection for a database as one of the schemas.
/ProviderType ProjectBased /ProjectName ProjectName.dbproj	Specify this syntax if you want to specify a database project as one of the schemas. If you want to import a file name that contains spaces, you must enclose the file name within quotation marks (for example, "My Project.dbproj").
/ProviderType FileBased /ProjectName ProjectName.dbschema	Specify this syntax if you want to specify a compiled .dbschema file as one of the schemas. If you want to import a file name that contains spaces, you must enclose the file name within quotation marks (for example, "My Project.dbschema").
/ProviderType FileBased /ProjectName ProjectName.dacpac	Specify this syntax if you want to specify a compiled output of a Data-tier Application project, a .dacpac file as one of the schemas. If you want to import a file name that contains spaces, you must enclose the file name within quotation marks (for example, "My Project.dacpac").

Additional Schema Compare Commands

The following table lists the additional DTE commands for Schema Compare and the parameters that you can specify for each:

Command	Parameter	Notes
SchemaCompareExportToEditor	None	Exports the update script to the Transact-SQL editor
SchemaCompareExportToFile	Filename	Exports the update script to the specified file
SchemaCompareFilterAllObjects	None	Removes all filters, all objects appear in the Schema Compare results
SchemaCompareFilterDifferentObjects	None	Only objects that are different appear in the Schema Compare results
SchemaCompareFilterEqualObjects	None	Only objects that are the same appear in the Schema Compare results
SchemaCompareFilterMissingObjects	None	Only objects that occur in the target but not in the source appear in the Schema Compare results
SchemaCompareFilterNewObjects	None	Only objects that occur in the source but not in the target appear in the Schema Compare results
SchemaCompareFilterNonSkipObjects	None	Only objects whose update action is not set to Skip appear in the Schema Compare results
SchemaCompareFilterSkipObjects	None	Only objects whose update action is set to Skip appear in the Schema Compare results
SchemaCompareNextChange	None	Scrolls the Schema Compare results to display the next difference
SchemaCompareObjectDefnHorizontal	None	Displays source and target object definitions side-by-side in the Object Definitions pane of the Schema Compare results
SchemaCompareObjectDefnOff	None	Hides display of the Object Definitions pane of the Schema Compare results
SchemaCompareObjectDefnVertical	None	Displays source and target object definitions one above the other in the Object Definitions pane of the Schema Compare results
SchemaCompareOptions	None	Opens the Schema Compare Options dialog box
SchemaComparePreviousChange	None	Scrolls the Schema Compare results to display the previous difference
SchemaCompareRefresh	None	Re-compares the source and target schemas
SchemaCompareRefreshScriptPreview	None	Updates the Schema Update Script pane of the Schema Compare results
SchemaCompareRestoreUpdateActionDefaults	None	Reverts any Update Actions to the state that they were in when the comparison completed
SchemaCompareRestoreUpdateSelectionDefaults	None	Reverts the Update Action for the specified rows to the state that they were in when the comparison completed
SchemaCompareShowUpdateScript	None	Opens the Schema Update Script window if it is not already visible
SchemaCompareSpecifySQLCMDVariables	None	Opens the dialog box that you can use to specify the values that should be used for SQLCMD variables when you compare schemas
SchemaCompareStop	None	Halts a schema comparison that is in progress
SchemaCompareWriteUpdates	None	Writes update to the target schema, if the target schema allows updates.

Comparing Database Data

You can use the Data.NewDataComparison command from the Visual Studio Command Window to compare the data in two databases. For more information, see How to: Compare and Synchronize the Data of Two Databases.

You can specify the following parameters for the Data.NewDataComparison and Data. DataCompareSelectSrcTgtDatabases commands:

Parameter	Required	Notes
/SrcServerName ServerName	Yes	Specify the name of the server that contains the data for the source of the comparison.
/SrcDatabaseName DatabaseName	Yes	Specify the name of the database that contains the data for the source of the comparison.
/SrcUserName UserName	Yes	Specify the user name that you want to use to connect to the database that contains the data for the source of the comparison.
/SrcPassword Password	Yes	Specify the password for the user name that you want to use to connect to the database that contains the data for the source of the comparison.
/SrcDisplayName SourceData	Yes	Specify the name that you want to appear in the Schema Compare window for the source of the comparison.
/TargetServerName ServerName	Yes	Specify the name of the server that contains the data for the target of the comparison.
/TargetDatabaseName DatabaseName	Yes	Specify the name of the database that contains the data for the target of the comparison.
/TargetUserName UserName	Yes	Specify the user name that you want to use to connect to the database that contains the data for the target of the comparison.
/TargetPassword Password	Yes	Specify the password for the user name that you want to use to connect to the database that contains the data for the target of the comparison.
/TargetDisplayName TargetData	Yes	Specify the name that you want to appear in the Schema Compare window for the target of the comparison.

Additional Data Compare Commands

The following table lists the additional DTE commands for Data Compare and the parameters that you can specify for each:

Command	Parameter	Notes
DataCompareExportToEditor	None	Exports the update script to the Transact-SQL editor.
DataCompareExportToFile	Filename	Exports the update script to the specified file.
DataCompareFilterAllRecords	None	Removes all filters. All objects appear in the Data Compare results.
DataCompareFilterDifferentRecords	None	Only records that are different appear in the Data Compare results.
DataCompareFilterIdenticalRecords	None	Only records that are identical appear in the Data Compare results.
DataCompareFilterOnlyInSourceRecords	None	Only records that appear in the source database but not in the target database appear in the Data Compare results.
DataCompareFilterOnlyInTargetRecords	None	Only records that appear in the target database but not in the source database appear in the Data Compare results.
DataCompareFilterSelectedRecords	None	Only the specified records appear in the Data Compare results.
DataCompareRefresh	None	Re-compares the data in the source and target database.
DataCompareShowUpdateScript	None	Displays the Data Update Script window if it is not already visible.
DataCompareStop	None	Halts a data comparison that is in progress.
DataCompareWriteUpdates	None	Writes update to the target database.
DataCompareDataCompareRefreshScriptPreview	None	Refreshes the script in the Data Update Script window.

Perform Refactoring Operations

The following sections describe the database refactoring operations that you can perform by using DTE commands and the parameters for each of those operations.

Rename Refactoring

The following table lists the parameters for the Data.RenameRefactor command:

Parameter	Notes
/AssumeProjectSchemaForScripts	If you specify this option, the objects are assumed to be in the default schema for the project if no schema is specified for the object. If you do not specify this option, the default schema is assumed to be dbo. This parameter is optional, and has a default value of 'true'. This parameter only affects scripts whose Build Action is not "Build". References in build scripts always assume the dbo schema.
/TargetObjectName objectName	Specifies the full name of the object, including schema, that you want to rename (for example, dbo.Table1).
/NewName newObjectName	Specifies the new name, excluding schema, for the object being renamed (for example, Table2).

For more information about how to use database refactoring to rename a database object, see Rename All References to a Database Object.

Fully Qualify Names

The following table lists the parameters for the Data.FullyQualifyNameRefactor command:

Parameter	Notes
/ScriptFilePath scriptPathAndFile	Specifies the full path and file name of the file in which you want to fully qualify names. For example: /ScriptFilePath "C:\Users\Username\Documents\Visual Studio 2010\Projects\MyDatabaseSolution\MyDatabaseProject\Schema Objects\Schemas\dbo\Views\MyView.view.sql"

For more information about how to use database refactoring to fully qualify object names, see Fully Qualify the Names of Database Objects.

Move Object to a New Schema

The following table lists the parameters for the Data.MoveSchemaRefactor command:

Parameter	Notes
/AssumeProjectSchemaForScripts	If you specify this option, the objects are assumed to be in the default schema for the project if no schema is specified for the object. If you do not specify this option, the default schema is assumed to be dbo. . This parameter is optional, and has a default value of 'true'. This parameter only affects scripts whose Build Action is not "Build". References in build scripts always assume the dbo schema.
/TargetObjectName objectName	Specifies the full name, including schema, of the object that you want to move to a new schema (for example, dbo.Table1).
/NewSchemaName schemaName	Specifies the schema to which you want to move the object.

For more information about how Move a Database Object to a Different Schema use database refactoring to move a database object to a different schema, see Move a Database Object to a Different Schema.

Rename Server and Database References

The following table lists the parameters for the Data.RenameServerDatabaseReferenceRefactor command:

Parameter	Notes
/OldServerName serverName	Specifies the server name that you want to replace.
/NewServerName serverName	Specifies the new server that you want to use instead of the old server.
/OldDatabaseName databaseName	Specifies the database name that you want to replace.
/NewDatabaseName databaseName	Specifies the new database that you want to use instead of the old database.

For more information about how to use database refactoring to replace database and server names with other names or with SQLCMD variables, see Rename References to a Server or Database.

Expand Wildcard Characters

The following table lists the parameters for the Data.WildcardExpansionRefactor command:

Parameter	Notes
/ScriptFilePath scriptPathAndFile	Specifies the full path and file name of the file in which you want to fully qualify names. The file name must be enclosed within double quotation marks. For example: /ScriptFilePath "C:\Users\Username\Documents\Visual Studio 2010\Projects\MyDatabaseSolution\MyDatabaseProject\Schema Objects\Schemas\dbo\Views\MyView.view.sql"

For more information about how to use database refactoring to expand wildcard characters in SELECT statements, see Expand Wildcard Characters in SELECT Statements.

Work with Partial Projects

The following table lists the additional DTE commands for partial projects and the parameters that you can specify for each:

Command	Parameter	Notes
Project.VSDBExportAsPartialProject	Filename	Exports the selection in Solution Explorer to the partial projects file that you specify.
Project.VSDBImportPartialProject	Filename	Imports the specified partial project (.files file) into the database project.
Project.VSDBRemovePartialProject	Filename	Removes the specified partial project from the database project.

For more information about partial projects, see Starting Team Development of Large Databases.

Working with Objects in Schema View

The following table lists the additional DTE commands for Schema View and the parameters that you can specify for each:

Command	Parameter	Notes
SchemaViewShowDependencies	None	Opens the Schema Dependency Viewer for the current selection in Schema View.
ShowBuiltInElementsInSchemaView	None	Toggles whether built-in database elements, such as the sys schema, appear in Schema View.
ShowExternalElementsInSchemaView	None	Toggles whether external elements, such as elements from a referenced database, appear in schema view.
DeleteObjectSchemaView	None	Deletes the selected object from Schema View, together with any objects that appear as children, based on the current sorting.
SynchronizeSchemaView	None	Synchronizes the objects in Schema View with the object definitions in the database projects in the solution. You only have to use this command if somehow Schema View ends up out of sync with the object definitions.
ToggleSchemaViewBySchema	None	Toggles the display in Schema View between sorting by schema and by object type.

Analyze Database Code

The following table lists the DTE commands that you can use to configure and apply database code analysis rules and the parameters that you can specify for each:

Command	Parameter	Notes
StaticCodeAnalysisConfigure	None	Displays the properties page that you can use to configure which rules to apply to your database project.
StaticCodeAnalysisRun	None	Applies the configured set of rules to your database project to identify common design, naming, and performing issues.

For more information about how you can use database code analysis, see Analyzing Database Code to Improve Code Quality.