Documentation

Table of Contents:


    Importing extended properties from SQL Server and Azure SQL

    2018-06-16 2018-09-28
    Applies to: 7.1 (current), Article available also for: 6.3

    This feature applies only to SQL Server, Azure SQL Database and Azure SQL Data Warehouse.

    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.

    Custom fields mapping

    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 options. You do it by checking Advanced options 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.

    Custom fields import window - empty

    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.

    Custom fields import window - filled

    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, press next and continue with import or update. Extended properties will be imported to defined custom fields.

    Found issue with this article? Comment below
    Comments (0)