Writing descriptions back to the database
Since version 6.0.1 writing descriptions back to the 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 them on the repository database. The 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 the 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 the 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. Unfortunately, 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 to the schema. Therefore, we decided not to post a script for MySQL.
