SQL Server Data Tools to Visual Studio 2013 - Database Reverse Engineering

Introduction

Database Cycle Management is hard.

Microsoft created SQL Server Data Tools for Visual Studio 2013, to make development easier.

• Single Tool to support developer’s needs
• Can build, debug, test, maintain, and refactor databases
• Developers can use familiar Visual Studio tools for database development
  • Code navigation, IntelliSense, C# language, platform-specific validation
    • Debugging, and declarative editing in the Transact-SQL editor
• Works connected or disconnected (Tools work on top of design time)
• Have Schema Model differencing capabilities (Compare and Update Model)
• Schema and app under TFS control
• Publish to all supported SQL platforms

STEP 1 - Create Project

Select SQL Server Database Project Template and call it DemoSSDT.

An empty project will be created.

STEP 2 - Import Database

After the project creation, we need to import from our SQL Server database.

Select the project and with the right mouse button, select the option Import. The import could be made from three different options. In this demo, we will choose Database, since we have an existent SQL Server Database.

A new screen will appear to select existent database. In this demo, we use the AdventureWorks2012.

Select the option Start.

The import will start.

After the import finishes, as we can see, all the schemas from our SQL Server database were imported to our Visual Studio project.

STEP 3 - Create Table

Create a new table on DBO Schema call Currency.

Select Add New Item on project solution.

This option will open a new screen with several items divided from categories.

Select the category Tables and Views and choose the option Table. Call it Currency.

Create a new table on DBO Schema an name it Currency.

The table was created with Id field pre generated.

Create two new fields (Code and Description).

For that on the table designer add two new fields, like in the SQL Server. As you see, the script is automatically created, when we change the designer.

STEP 4 - Refactoring

One of the new features is the possibility to use refactoring.

Select one field on the script tab and with the right mouse button, select the option refactor like on the image below. One of the options is the Refactor option. This option like on the other Visual Studio Project, allows the user to rename in all solution (Functions, Store Procedures, …) the field of our table.

STEP5 - Publish

After all our changes on the project, we could in an easy way Publish to SQL Server database the changes made on our project.

Select the project and select the option Publish.

Select the target database. On our case will be the AdventureWorks2012 existent on SQL Server database.

Choose the Publish button.

On the output window, we have access to result of our publish. One of the options give access to the script generated. If we click on it, we will see the script generated like we see in the image below.

On the left tab, as we can see, the Currency table was published to our database, with the three fields created.

References

• Microsoft SQL Server Data tools
• VS and SSDT versions
• Team Blog and Forums