Writing descriptions back to the database

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.