Importing extended properties from SQL Server and Azure SQL
This feature applies only to SQL Server, Azure SQL Database and Azure Synapse Analytics (formerly SQL DW).
Extended properties - custom fields
SQL Server has a unique functionality of extended properties - user-defined metadata fields attached to various schema elements - tables, columns, stored procedures, etc. Dataedo has a similar function - custom fields. You can mix them both and exchange custom fields/extended properties between the Dataedo repository and SQL Server database using the Import and export extended properties function.
Mapping
To import extended properties, you need to map their names to custom fields. The table below shows this concept.
Mapping is saved between imports and is also used for .exporting extended properties.
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 reads this field and imports it to the Description field by default at each import so there is no need to define this property.
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 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.
Importing extended properties
Extended properties are imported in the process of import or update of the database schema.
To define custom field - extended properties mappings you need to enter advanced settings. You do it by checking the Advanced settings option in the connection window.
If you don't have any custom fields defined in your repository then the next screen will show you an empty list.
If you want to import extended properties you need to define custom fields first. You can do it by clicking the Define custom fields button. This will open the configurator documented in a separate article. Once you have added custom fields to your repository you can now map extended properties. First, select which fields you want to import and then provide the name of the extended property for each.
You can find out the names of extended properties in your database with this SQL query:
select distinct name
from sys.extended_properties
order by name
Once defined, click the next button. If you're running a first import of documentation, continue normally. However, if you're adding extended properties to existing documentation, you will also need to check the Reimport all objects box on the next screen:
Continue as usual, and the extended properties will be imported to the defined custom fields.
