2

In a question on StackOverflow I found this query, that displays table and column description for all the tables in current database:

SELECT      u.name + '.' + t.name AS [table],
            td.value AS [table_desc],
            c.name AS [column],
            cd.value AS [column_desc]
FROM        sysobjects t
INNER JOIN  sysusers u
    ON      u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
    ON      td.major_id = t.id
    AND     td.minor_id = 0
    AND     td.name = 'MS_Description'
INNER JOIN  syscolumns c
    ON      c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
    ON      cd.major_id = c.id
    AND     cd.minor_id = c.colid
    AND     cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY    t.name, c.colorder

This helps me a lot because I immediately see all the tables and columns that have no description. Now, it would be really cool to be able to directly edit these descriptions. Is there a software that allows bulk editing of descriptions for tables and/or columns?

2
  • 1
    Bulk editing how? If there is something you can programmatically do to add a description to every column of every table without having to think about it, what value could the description possibly have? (To be honest, documentation around your schema belongs in a data dictionary / source control, not extended properties.) Dec 1 '15 at 15:28
  • Not programmatically. I am only looking for a convenient way to enter the information, like a grid where I can immediately see the blanks and manually fill them. Dec 2 '15 at 8:44
5

You can't add them in mass but you can generate the commands to do so pretty easily. I've added two extra columns to the query that have the sys.sp_addextendedproperty commands added if there isn't already a description. You would still have to manually modify the description value for each but this should save you some time.

SELECT      u.name + '.' + t.name AS [table],
            td.value AS [table_desc],
            CASE WHEN td.value IS NULL THEN 
                    'EXEC sys.sp_addextendedproperty @name=N''TableDescription'', ' + 
                        '@value=N''This is the table description'' , ' + 
                        '@level0type=N''SCHEMA'',@level0name=N'''+QUOTENAME(u.name)+''', ' + 
                        '@level1type=N''TABLE'',@level1name=N'''+QUOTENAME(t.name)+'''' END AS CreateTableNotes,
            c.name AS [column],
            cd.value AS [column_desc],
            CASE WHEN cd.value IS NULL THEN 
                    'EXEC sys.sp_addextendedproperty @name=N''ColumnDescription'', ' +
                        '@value=N''This is the column description'' , ' +
                        '@level0type=N''SCHEMA'',@level0name=N'''+QUOTENAME(u.name)+''', ' + 
                        '@level1type=N''TABLE'',@level1name=N'''+QUOTENAME(t.name)+''', ' +
                        '@level2type=N''COLUMN'',@level2name=N'''+QUOTENAME(c.name)+'''' END AS CreateColumnNotes
FROM        sysobjects t
INNER JOIN  sysusers u
    ON      u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
    ON      td.major_id = t.id
    AND     td.minor_id = 0
    AND     td.name = 'MS_Description'
INNER JOIN  syscolumns c
    ON      c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
    ON      cd.major_id = c.id
    AND     cd.minor_id = c.colid
    AND     cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY    t.name, c.colorder
2
  • Why do you use TableDescription instead of MS_Description? Dec 2 '15 at 9:28
  • Just a typo. I didn't notice the MS_Description in the JOIN. It's just a descriptor field so I didn't worry about it when creating the command. Dec 3 '15 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.