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