Table of Contents:
Exporting extended properties to SQL Server
Dataedo allows writing its custom fields back to SQL Server database extended properties. This is a part of exporting descriptions function.
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.
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.
Mapping is saved between exports and imports.
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.
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.
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.
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.