Documentation

Mass description update

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

Table of Contents:


    Many databases often have same columns in many tables. Examples of such columns are CreatedDate, CreatedBy, ModifiedDate, etc. It is desired to update column descriptions in all tables at once. Dataedo Editor doesn't have that option but there's a simple trick. Dataedo repository is an SQL Server database and it is possible to make updates directly in the database. Below is a simple script that does the job.

    Updating descriptions

    To update the description of multiple columns with the same name at once connect to Dataedo repository with database console (for instance with SQL Server Management Studio) and execute this script replacing example strings with:

    • new description,
    • column name,
    • documentation title (this is what you see in tree navigation - to copy title select documentation, press F2, then Ctrl+C and Esc)

    in the right character string.

    update columns
       set description = 'My new description' -- your new description
      from columns c 
           inner join tables t on c.table_id = t.table_id
           inner join databases d on d.database_id = t.database_id
     where c.name = 'ModifiedDate' -- your column name
       and d.title = 'AdventureWorks' -- your documentation title
       and len(isnull(c.description,'')) = 0 -- update only blank descriptions, 
       -- remove if you want to update all columns
    

    Query updates only columns with the blank description.

    Tip. To update all columns, regardless of their descriptions, if they are empty or not, remove the last condition (len(isnull(c.description,'')) = 0).

    Previewing descriptions

    Use this query to preview descriptions of a column in all the tables:

    select t.[schema] as schema_name,
           t.name as table_name,
           c.name as column_name,
           c.datatype,
           c.description
      from columns c 
           inner join tables t on c.table_id = t.table_id
           inner join databases d on d.database_id = t.database_id
     where c.name = 'ModifiedDate' -- your column name
       and d.title = 'AdventureWorks' -- your documentation title
     order by table_name
    
    Found issue with this article? Comment below
    Comments (0)