Exporting extended properties to SQL Server

Applies to: Dataedo 23.x versions, Article available also for: 24.x (current), 10.x
You are looking at documentation for an older release.
Switch to the documentation for Dataedo 24.x (current).

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

Extended properties

SQL Server databases have a feature called extended properties. Those are custom metadata fields DBA/architect 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 extended property name provided will be exported. Table below shows this concept.

Custom fields mapping

Mapping is saved between exports and imports.

MS_Description

SQL Server 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 first item in the table).

Repository vs database scope

Custom fields are defined globally for 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 trucated 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 SQL Server extended properties is done through Export descriptions/custom fields to database option described in 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 field you need to provide mapping to extended properties. You do it by checking 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.

Custom fields mapping

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 MS_Description property just uncheck it.