Lukasz Gil
Many developers (especially working with Microsoft technologies and Windows applications) are familiar with Visual Studio. This is the most popular IDE for software development in .NET but also provides tools for coding on many other platforms and programming languages. In this tutorial, I will show you how Developers, without changing IDE can describe database objects so that other team members (f.e. DBA's) can read it from the database with a console or some other tools. This tutorial will show you how to enter descriptions for tables and columns using Visual Studio and then generate and distribute nicely formatted documentation with Dataedo. All screens are from VS 2015 (but should be similar in other versions).
Every point in the diagram has a reference in the paragraphs below. It is marked with a number in the brackets. The flow of the data looks like this:
Every step in the diagram has a reference in the paragraphs below, in the brackets.
Create Database Project
First, you have to create Database Project in Visual Studio. To achieve this, follow few simple steps below. If you have already had Database project, just move to the next section.
- Choose SQL Server Database Project template from File -> New -> Project menu, choose proper location and name and click OK.
- Right click on your project in Solution Explorer and select properties. Then on Project Settings choose proper SQL Server Target platform
- Open Import Database window by right click on your project -> Import -> Database. Then click on Select Connection button.
- Choose one of your recent connections on History tab or use Browse tab to add a new one by putting connection details. You can choose one of your local, network or Azure servers too.
For more information about Visual Studio Database Projects, check links below.
Describe objects
Now you have your DB schema imported, and you can enter descriptions to your database objects. In Solution Explorer, you can see all your tables and other objects.
The main grid shows table properties. This is where you will edit descriptions. First, you need to show Descriptions column by right-clicking on the free space below table and selecting Description option.
This will show a new column where you will be able to edit all column descriptions.
To edit table description, just click on properties tab on the right and enter your text in Description field.
Save descriptions to the database [step 1]
When you are done with describing tables, it is time to save changes to the database. Build your project (Ctrl + Shift + B), and when it is successful, then you can publish changes you made to the physical database. Right-click on your project and then click Publish. In the window that pops up, you need to select database where you want to save your descriptions.
Click on Edit... button to select your database (you will see the same window as in the previous paragraph when you chose your source database). Now you can click on Generate Script to get SQL script which you can run by yourself, or you can click Publish button to save descriptions to the database automatically.
You have completed the first part of the tutorial – you put descriptions to your database. Now let’s export them to a nice HTML or PDF format.
Import to Dataedo (first time) [step 3]
Descriptions, which you save in database, are stored in extended property 'MS_Description'. It's the standard property, which is used by SQL Server. It can be read by Dataedo in your database documentation. Now you can import your database and create documentation.
- Run Dataedo and import your database schema. Click on Add documentation button and provide connection details. When importing is finished, all your descriptions will be imported.
- Now you can see all your description in editor.
You might have noticed that we skipped step 2 where we clear descriptions in Dataedo repository. We don't need to do it right now as this is our first import. We'll get back to that.
Export documentation [step 4]
Now you can export your documentation using one of the formats and templates provided. Just click on Export documentation button on the ribbon.
Click Next, and then choose one of the templates.
After the export is completed, you have nice formatted document, which contains all your descriptions.
You can also export to HTML (get trial first):
Repeat and automate the process
You can repeat the process as often as you want and even schedule it to run automatically. Learn more about automation.
Importing schema to the repository is different the second time you do it. After initial import, you are updating the schema in existing documentation. There's one thing you need to do before that update - clear existing descriptions.
Clear descriptions in Dataedo repository [step 2]
In standard use of Dataedo, all descriptions are stored and edited in a repository, and descriptions from the database are imported just at the first time. After that Dataedo is a master source of descriptions. If you want your descriptions to be updated in your database extended properties, then you need to clear descriptions in Dataedo repository so that the update can read them from the database again. You do it by running a simple SQL script on the repository - more on that here.
Reimport descriptions and schema from database [step 3]
Now you need to reimport schema and descriptions to Dataedo repository. Select your documentation on the navigation tree on the left and click Update documentation. Connection details are already there. Hit Next a few times, and your documentation is up to date, just ready for export.
Export documentation [step 4]
Now you can repeat step 4 the same way you did before. You have now a complete guide on how to describe your database in Visual Studio and then publish documentation in HTML or PDF with your team. Good luck.
