Documentation

Table of Contents:


    Writing descriptions back to the database

    2018-08-23
    Applies to: 7.1 (current), Article available also for: 6.3

    Note

    Since version 6.0.1 writing descriptions back to database is supported by the tool. This article describes a workaround for earlier versions.

    Thanks to the open access to our repository you can do it by yourself right now.

    We've created scripts that generate SQL commands adding your table, view and column descriptions to either SQL Server extended properties or Oracle DB comments.

    To write descriptions back to the database, first you need to copy/paste our scripts and execute it on repository database. Output of this script will contain SQL commands you would then copy and execute on your database. This script will add/update extended properties/comments.

    Do note that both extended properties and comments are plaintext and limited in size, so the copy will lose formatting and may be truncated. Additionally, Dataedo 5 stores a plaintext description of major objects (tables, procedures, but not columns and parameters) only in lowercase.

    SQL Server

    Execute this script on Dataedo repository database:

    DECLARE @title AS NVARCHAR(250) = 'Dataedo repository'; --Put title of your documentation here (should be unique)
    --table descriptions
    SELECT CONCAT (
            'IF NOT EXISTS (SELECT 1  FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('''
            ,replace(tbl.[schema], '''', '''''')
            ,'.'
            ,replace(tbl.[name], '''', '''''')
            ,''') AND [name] = N''MS_Description'' AND [minor_id] = 0) '
            ,'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N'''
            ,replace(left(tbl.[description_search], 3498), '''', '''''')
            ,''' , @level0type=N''SCHEMA'',@level0name=N'''
            ,replace(tbl.[schema], '''', '''''')
            ,''', @level1type=N''TABLE'',@level1name=N'''
            ,replace(tbl.[name], '''', '''''')
            ,''' ELSE '
            ,'EXEC sys.sp_updateextendedproperty @name=N''MS_Description'', @value=N'''
            ,replace(left(tbl.[description_search], 3498), '''', '''''')
            ,''' , @level0type=N''SCHEMA'',@level0name=N'''
            ,replace(tbl.[schema], '''', '''''')
            ,''', @level1type=N''TABLE'',@level1name=N'''
            ,replace(tbl.[name], '''', '''''')
            ,''';'
            ) AS tsql_command
    FROM dbo.[tables] tbl
    INNER JOIN dbo.[databases] db
        ON db.database_id = tbl.database_id
    WHERE tbl.[description_search] IS NOT NULL
        AND tbl.[status] = 'A'
        AND tbl.[object_type] = 'TABLE'
        AND db.[title] = @title
    --table column descriptions
    UNION ALL
    SELECT CONCAT (
            'IF NOT EXISTS (SELECT 1  FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('''
            ,replace(tbl.[schema], '''', '''''')
            ,'.'
            ,replace(tbl.[name], '''', '''''')
            ,''') AND [name] = N''MS_Description'' AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = '''
            ,replace(col.[name], '''', '''''')
            ,''' AND [object_id] = OBJECT_ID('''
            ,replace(tbl.[schema], '''', '''''')
            ,'.'
            ,replace(tbl.[name], '''', '''''')
            ,'''))) '
            ,'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N'''
            ,replace(left(col.[description], 3498), '''', '''''')
            ,''' , @level0type=N''SCHEMA'',@level0name=N'''
            ,replace(tbl.[schema], '''', '''''')
            ,''', @level1type=N''TABLE'',@level1name=N'''
            ,replace(tbl.[name], '''', '''''')
            ,''', @level2type=N''COLUMN'',@level2name=N'''
            ,replace(col.[name], '''', '''''')
            ,''' ELSE '
            ,'EXEC sys.sp_updateextendedproperty @name=N''MS_Description'', @value=N'''
            ,replace(left(col.[description], 3498), '''', '''''')
            ,''' , @level0type=N''SCHEMA'',@level0name=N'''
            ,replace(tbl.[schema], '''', '''''')
            ,''', @level1type=N''TABLE'',@level1name=N'''
            ,replace(tbl.[name], '''', '''''')
            ,''', @level2type=N''COLUMN'',@level2name=N'''
            ,replace(col.[name], '''', '''''')
            ,''';'
            ) AS tsql_command
    FROM dbo.[tables] tbl
    INNER JOIN dbo.[columns] col
        ON col.table_id = tbl.table_id
    INNER JOIN dbo.[databases] db
        ON db.database_id = tbl.database_id
    WHERE col.[description] IS NOT NULL
        AND tbl.[status] = 'A'
        AND col.[status] = 'A'
        AND tbl.[object_type] = 'TABLE'
        AND db.[title] = @title
        --view descriptions
    UNION ALL
    SELECT CONCAT (
            'IF NOT EXISTS (SELECT 1  FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('''
            ,replace(tbl.[schema], '''', '''''')
            ,'.'
            ,replace(tbl.[name], '''', '''''')
            ,''') AND [name] = N''MS_Description'' AND [minor_id] = 0) '
            ,'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N'''
            ,replace(left(tbl.[description_search], 3498), '''', '''''')
            ,''' , @level0type=N''SCHEMA'',@level0name=N'''
            ,replace(tbl.[schema], '''', '''''')
            ,''', @level1type=N''VIEW'',@level1name=N'''
            ,replace(tbl.[name], '''', '''''')
            ,''' ELSE '
            ,'EXEC sys.sp_updateextendedproperty @name=N''MS_Description'', @value=N'''
            ,replace(left(tbl.[description_search], 3498), '''', '''''')
            ,''' , @level0type=N''SCHEMA'',@level0name=N'''
            ,replace(tbl.[schema], '''', '''''')
            ,''', @level1type=N''VIEW'',@level1name=N'''
            ,replace(tbl.[name], '''', '''''')
            ,''';'
            ) AS tsql_command
    FROM dbo.[tables] tbl
    INNER JOIN dbo.[databases] db
        ON db.database_id = tbl.database_id
    WHERE tbl.[description_search] IS NOT NULL
        AND tbl.[status] = 'A'
        AND tbl.[object_type] = 'VIEW'
        AND db.[title] = @title
    --view column descriptions
    UNION ALL
    SELECT CONCAT (
            'IF NOT EXISTS (SELECT 1  FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('''
            ,replace(tbl.[schema], '''', '''''')
            ,'.'
            ,replace(tbl.[name], '''', '''''')
            ,''') AND [name] = N''MS_Description'' AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = '''
            ,replace(col.[name], '''', '''''')
            ,''' AND [object_id] = OBJECT_ID('''
            ,replace(tbl.[schema], '''', '''''')
            ,'.'
            ,replace(tbl.[name], '''', '''''')
            ,'''))) '
            ,'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N'''
            ,replace(left(col.[description], 3498), '''', '''''')
            ,''' , @level0type=N''SCHEMA'',@level0name=N'''
            ,replace(tbl.[schema], '''', '''''')
            ,''', @level1type=N''VIEW'',@level1name=N'''
            ,replace(tbl.[name], '''', '''''')
            ,''', @level2type=N''COLUMN'',@level2name=N'''
            ,replace(col.[name], '''', '''''')
            ,''' ELSE '
            ,'EXEC sys.sp_updateextendedproperty @name=N''MS_Description'', @value=N'''
            ,replace(left(col.[description], 3498), '''', '''''')
            ,''' , @level0type=N''SCHEMA'',@level0name=N'''
            ,replace(tbl.[schema], '''', '''''')
            ,''', @level1type=N''VIEW'',@level1name=N'''
            ,replace(tbl.[name], '''', '''''')
            ,''', @level2type=N''COLUMN'',@level2name=N'''
            ,replace(col.[name], '''', '''''')
            ,''';'
            ) AS tsql_command
    FROM dbo.[tables] tbl
    INNER JOIN dbo.[columns] col
        ON col.table_id = tbl.table_id
    INNER JOIN dbo.[databases] db
        ON db.database_id = tbl.database_id
    WHERE col.[description] IS NOT NULL
        AND tbl.[status] = 'A'
        AND tbl.[object_type] = 'VIEW'
        AND db.[title] = @title;
    

    Execute the results on your database.

    Full version of the script, which includes more objects (like procedures, parameters, triggers etc.) can be downloaded below: Export_descriptions_to_SQLserver.sql

    Oracle

    Execute this script on Dataedo repository database:

    DECLARE @title AS NVARCHAR(250) = 'Dataedo repository'; --Put title of your documentation here (should be unique)
    --table descriptions
    SELECT CONCAT (
            'COMMENT ON TABLE '
            ,tbl.[schema]
            ,'.'
            ,tbl.[name]
            ,' IS '''
            ,replace(left(tbl.[description_search], 4000), '''', '''''')
            ,''';'
            ) AS oracle_sql_command
    FROM dbo.[tables] tbl
    INNER JOIN dbo.[databases] db
        ON db.database_id = tbl.database_id
    WHERE tbl.[description_search] IS NOT NULL
        AND tbl.[status] = 'A'
        AND db.[title] = @title
    --column descriptions
    UNION ALL
    SELECT CONCAT (
            'COMMENT ON COLUMN '
            ,tbl.[schema]
            ,'.'
            ,tbl.[name]
            ,'.'
            ,col.[name]
            ,' IS '''
            ,replace(left(col.[description], 4000), '''', '''''')
            ,''';'
            ) AS oracle_sql_command
    FROM dbo.[tables] tbl
    INNER JOIN dbo.[columns] col
        ON col.table_id = tbl.table_id
    INNER JOIN dbo.[databases] db
        ON db.database_id = tbl.database_id
    WHERE col.[description] IS NOT NULL
        AND tbl.[status] = 'A'
        AND col.[status] = 'A'
        AND db.[title] = @title;
    

    Execute the results on your database.

    Downloadable version: Export_descriptions_to_OracleDB.sql

    MySQL

    We tried our best to write a script for MySQL as well. Unfortunatelly, the nature of the statements that add comments in this platform are a bit dangerous. Alter statements need to include all column attributes and it poses a threat of making unwanted changes of the schema. Therefore we decided not to post a script for MySQL.

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