Importing extended properties from SQL Server and Azure SQL

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 similar function - custom fields. You can mix them both and exchange custom fields/extended properties between Dataedo repository and SQL Server database using Import and export extended properties function.

Mapping

To import extended properties you need to map their names to custom fields. 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 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 entier 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 proces of import or update of database schema.

To define custom field - extended properties mappings you need to enter advanced settings. You do it by checking Advanced settings option in the connection window.

If you don't have any custom fields defined in your repository then next screen will show you empty list.

If you want to import extended properties you need to define custom fields first. You can do it by clicking Define custom fields button. This will open configurator documented in separate article. Once you added custom fields to your repository you can now map extended properties. First, select which fields you want to import and then provide name of extended property for each.

You can find out 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 a documentation, continue normally. However, if you're adding extended properties to an 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 defined custom fields.