Exporting extended properties to Azure SQL

Dataedo allows writing its custom fields back to Azure database extended properties. This is a part of exporting descriptions function.

Extended properties

Azure SQL databases have a feature called extended properties. Those are custom metadata fields DBA/architects can assign to various database schema elements such as tables or columns.

Mapping

To export custom fields, you need to map them to extended properties. Only fields which have an extended property name provided will be exported. The table below shows this concept.

Mapping is saved between exports and imports.

MS_Description

Azure SQL database has a special extended property named MS_Description that is used by MS tools as a default field for schema element descriptions. Dataedo maps this property to its descriptions both in import and export (note the first item in the table).

Repository vs database scope

Custom fields are defined globally for the entire repository which can hold multiple databases. Mapping custom fields to extended properties, however, is defined (and saved) per database/documentation. This means that each database can use different names for extended properties, and for each database, you can choose which properties you want to import.

String length

Databases have different limitations in terms of maximum string length. Dataedo fields are longer, and it is possible that descriptions exported to your database may be truncated if their length exceeds database capabilities.

Required permissions

ALTER permissions on objects you want to add extended property to (tables, views, functions, procedures, triggers).

Exporting custom fields/extended properties

Exporting custom fields to Azure SQL extended properties is done through the Export descriptions/custom fields to database option described in the Export descriptions to database article.

If you have custom fields defined in your Dataedo repository, then they will appear in the list of fields. To export a field, you need to provide mapping to extended properties. You do it by checking the specific field and typing in an extended property name.

Export will either overwrite existing properties or create new ones for objects from your repository.

You can find out what names of extended properties are already in use in your database with this SQL query:

select distinct name
  from sys.extended_properties
 order by name

If you have imported or exported extended properties for this database before, those mappings are saved and will be provided in the table.

Note that on the list of custom fields there is always a Description item and it's selected by default. If you want to export specific custom fields but don't want to change the MS_Description property, just uncheck it.