Mass description update

Applies to: Dataedo 23.x (current) versions, Article available also for: 10.x

Many databases often have the 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 are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.