Add Column Descriptions in SQL Server

(Show me the code: Column Search with Descriptions Gist)

In my previous post, I described how to use a query quickly search for columns in a database.  The query output was a nice little table reporting the columns, data type, null constraints, defaults, indexes, and foreign key references.  Let me tell you, I've copied and pasted this result set into many a design document in my days.  The only thing missing from this table is a description for each of the columns.

SQL Server’s Extended Properties to the rescue!  Extended properties are simple key value pairs you can add to basically any entity in the database (tables, views, columns, triggers, stored procedures, functions, indexes, the database itself, any many more).  Just right click on almost any node in the Object Explorer (F8) tree, select Properties, and go to the Extended Properties page.  You can add any key name you want.  In this article we’ll be adding a Description property to Columns.

Adding Extended Properties via the Object Explorer works fine, but my preferred method is to use a stored procedure.  Here is command:

exec sp_AddExtendedProperty 'Description', @description
  , 'SCHEMA', @schemaName
  , 'TABLE', @tableName
  , 'COLUMN', @columnName

A problem with the Add Extended Property stored procedure is that it fails if the column already has the extended property defined.  There is an Update Extended Property stored procedure, but it fails if the column doesn’t already have the extended property defined.  Here is a temporary stored procedure that updates a column’s Description extended property regardless of whether it is defined yet or not.

Create Procedure #SetColumnDescription(
  @schemaName sysname
  , @tableName sysname
  , @columnName sysname
  , @description sql_variant
)
As
    If Exists (
      Select 1 
      From fn_listextendedproperty('Description'
        , 'SCHEMA', @schemaName
        , 'TABLE', @tableName
        , 'COLUMN', @columnName
      )
    )
        exec sp_DropExtendedProperty 'Description'
          , 'SCHEMA', @schemaName
          , 'TABLE', @tableName
          , 'COLUMN', @columnName

    If (Not @description Is Null) And (Not @description = '')
        exec sp_AddExtendedProperty 'Description', @description
          , 'SCHEMA', @schemaName
          , 'TABLE', @tableName
          , 'COLUMN', @columnName
GO

As you may have figured out from the temporary stored procedure, to read the Description back use the following table function:

Select value From fn_listextendedproperty('Description'
  , 'SCHEMA', @schemaName
  , 'TABLE', @tableName
  , 'COLUMN', @columnName
)

So this function lookup can be added to the Select clause of my column search query to add a Description column to the output.  Here is a new version of the column search query with the Description column added: Column Search with Descriptions Gist

Also embedded in this query is a commented out column to generate the call to the SetColumnDescription stored procedure.  The definition of the SetColumnDescription procedure is commented out at the bottom of the file.  In my day to day work I copy the SetColumnDescription definition to a new query window, flip back to the column search query, run it to generate the SetColumnDescription commands, then I copy and paste those commands to the SetColumnDescription query windows.  Here I edit/create the column descriptions in the query window and run the script to apply those descriptions to the database.  There is also a commented out filter in the Where clause to limit the results to columns that don’t have a Description defined yet.

And with the power of Extended Properties we’ve just added Column Descriptions to our database design and turned the Column Search query into a power database design living document.  I hope you find this useful.

Print | posted on Thursday, February 27, 2014 10:12 PM

Feedback

# re: Add Column Descriptions in SQL Server

Left by Maurónimo at 3/27/2015 1:34 PM
Gravatar Thank you for sharing this code Timothy, it's very very Handy; Im glad I've found your site.

# re: Add Column Descriptions in SQL Server

Left by C Snyder at 11/18/2015 9:54 AM
Gravatar To get the Description property to display in the SSMS (I'm using the 2014 version) table designer description field, you need to use [b]'MS_Description'[/b] for the property name.

If building the table from a script, you will still need to create the extended prop, 'MS_Description', using the sp_AddXX stored procedure. If you are updating the value, you will need to use the sp_UpdateXX stored proc.

Example:
exec sys.sp_AddExtendedProperty
@name=N'MS_Description', @value=N'Your Description Here' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'YourTableName',
@level2type=N'COLUMN',@level2name=N'YourColumnName'

exec sys.sp_UpdateExtendedProperty
@name=N'MS_Description', @value=N'Your Description Here' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'YourTableName',
@level2type=N'COLUMN',@level2name=N'YourColumnName'

Tip: Put a value in the description field, use the context menu to generate the create table script. That shows the fields and sp's you need

# re: Add Column Descriptions in SQL Server

Left by Timothy Klenke at 11/19/2015 11:37 AM
Gravatar Thanks C Snyder. Great tip. I've modified the Gist code (link at the top) so that the query uses the 'MS_Description' property name instead of 'Description'. As you said, this then shows the description in the SSMS Table Designer.

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 1/25/2018 2:52 AM
Gravatar Incredible Information sharing .. I am exceptionally glad to peruse this article .. a debt of gratitude is in order for giving us experience info.Fantastic pleasant. I welcome this post. Nursing Tips

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 1/26/2018 5:25 AM
Gravatar Much obliged to you for setting aside an ideal opportunity to distribute this data exceptionally helpful! slak construction company

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 1/26/2018 7:05 AM
Gravatar Much obliged such a great amount for sharing this marvelous information! I am anticipating see more postsby you! betboo giris

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 1/27/2018 10:11 AM
Gravatar You have a decent point here!I absolutely concur with what you have said!!Thanks for sharing your views...hope more individuals will read this article!!! mobil bahis oyna

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 1/28/2018 8:56 AM
Gravatar I am extremely glad to find your post as it will get to be on top in my accumulation of most loved online journals to visit. voyance pas cher

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 1/28/2018 9:50 AM
Gravatar much thanks to you for your fascinating infomation. Justinbet canli bahis

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 1/29/2018 4:10 AM
Gravatar Much thanks to you for setting aside an ideal opportunity to distribute this data extremely valuable! casino dunya yeni giriş

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 1/30/2018 4:40 AM
Gravatar This is extremely fascinating substance! I have completely appreciated perusing your focuses and have arrive at the conclusion that you are right about large portions of them. You are extraordinary. Offer

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 1/30/2018 5:05 AM
Gravatar I high value this post. It's elusive the great from the terrible now and then, however I think you've nailed it! would you brain overhauling your web journal with more data? Baltimore TV Mounting Installation

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 1/30/2018 5:38 AM
Gravatar Completely awesome posting! Bunches of helpful data and motivation, both of which we all need!Relay welcome your work. ico bitcoin

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 1/30/2018 9:45 AM
Gravatar I might want to thank you for the endeavors you have made in composing this article. I am trusting the same best work from you later on too.. fly casting fishing

# SEO

Left by Robinjack at 1/31/2018 6:46 AM
Gravatar would you brain overhauling your web journal with more data? Online CPR Certification

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 1/31/2018 11:43 AM
Gravatar Great artcile, yet it would be better if in future you can share more about this subject. Continue posting. personal injury solicitor

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/1/2018 9:07 AM
Gravatar Thanks for this great post, i find it very interesting and very well thought out and put together. I look forward to reading your work in the future. 1xbet bahis giris

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/1/2018 9:07 AM
Gravatar Undoubtedly this is a phenomenal post I got a considerable measure of learning subsequent to perusing good fortunes. Subject of online journal is astounding there is just about everything to peruse, Brilliant post. Tempobet giris

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/2/2018 5:02 AM
Gravatar I am unquestionably making the most of your site. You unquestionably have some extraordinary knowledge and incredible stories. luxury rent

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/2/2018 9:26 AM
Gravatar This is a savvy blog. I would not joke about this. You have such a great amount of learning about this issue, thus much energy. You additionally know how to make individuals rally behind it, clearly from the reactions. bahis siteleri

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/3/2018 9:26 AM
Gravatar Hey what a splendid post I have run over and trust me I have been scanning out for this comparative sort of post for recent week and scarcely ran over this. Much thanks and will search for more postings from you. CPR Certification

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/4/2018 4:46 AM
Gravatar An interesting dialogue is price comment. I feel that it is best to write more on this matter, it may not be a taboo topic however usually individuals are not enough to talk on such topics. To the next. Cheers. medication

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/4/2018 12:21 PM
Gravatar Much obliged to you for some other educational web journal. Where else would I be able to understand that sort of data written in such a perfect means? I have a mission that I'm a little while ago chipping away at, and I have been at the post for such data. medication

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/5/2018 2:58 AM
Gravatar A debt of gratitude is in order for the important data and bits of knowledge you have so given here... en kaliteli casino siteleri

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/5/2018 9:46 AM
Gravatar Most likely this is a phenomenal post I got a ton of learning in the wake of perusing good fortunes. Subject of website is magnificent there is very nearly everything to peruse, Brilliant post. online bahis siteleri

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/9/2018 2:55 AM
Gravatar At Concourse Optometry we stock the latest styles from many name brands. Whatever your needs, we will find the best looking, best fitting pair of eyeglasses for you! Here is a list of the brands we carry: article for content

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/9/2018 3:51 AM
Gravatar I’ve been searching for some decent stuff on the subject and haven't had any luck up until this point, You just got a new biggest fan!.. curiousjordy

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/9/2018 4:32 AM
Gravatar Most likely this is a phenomenal post I got a ton of learning in the wake of perusing good fortunes. Subject of website is magnificent there is very nearly everything to peruse, Brilliant post. online bahis oyna

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/9/2018 5:09 AM
Gravatar Pleasant learning picking up article. This post is truly the best on this significant theme. bahis oyna

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/13/2018 1:40 AM
Gravatar A debt of gratitude is in order for offering this quality data to us. I truly delighted in perusing. Will most likely going to impart this URL to my companions. kaçak bahis oyna

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/13/2018 1:40 AM
Gravatar Your article has provoked a great deal of positive hobby. I can see why since you have made such a decent showing of making it intriguing. tanning lotion

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/13/2018 1:40 AM
Gravatar This is a splendid web journal! I'm extremely content with the remarks!.. wrightchirogr

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/14/2018 10:54 AM
Gravatar I am upbeat to discover this post exceptionally helpful for me, as it contains part of data. I generally want to peruse the quality substance and this thing I found in you post. A debt of gratitude is in order for sharing. repair near me

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/16/2018 7:04 AM
Gravatar This article is an engaging abundance of educational information that is fascinating and elegantly composed. I compliment your diligent work on this and thank you for this data. You have what it takes to get consideration. canli bahis siteleri

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/17/2018 6:23 AM
Gravatar Amazing site, Distinguished criticism that I can handle. Im pushing ahead and may apply to my present place of employment as a pet sitter, which is exceptionally charming, however I have to extra extend. Respects. casinometropol canlı casino

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/20/2018 11:32 AM
Gravatar Most likely this is a phenomenal post I got a ton of learning in the wake of perusing good fortunes. Subject of website is magnificent there is very nearly everything to peruse, Brilliant post. canlı bahis siteleri

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/23/2018 2:22 AM
Gravatar You have a decent point here!I thoroughly concur with what you have said!!Thanks for sharing your views...hope more individuals will read this article!!! illegal iddaa siteleri

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/23/2018 2:22 AM
Gravatar The data you have posted is extremely helpful. The destinations you have alluded was great. A debt of gratitude is in order for sharing... online casino siteleri

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/23/2018 9:30 AM
Gravatar Hola que Tal! Curso Intensivo de Espanhol em Sao Paulo SP. Rapido, Interativo e divertido espanhol intensivo

# re: Add Column Descriptions in SQL Server

Left by games banat at 2/24/2018 3:20 AM
Gravatar The data you have posted is extremely helpful. The destinations you have alluded was great. A debt of gratitude is in order for sharing.
This Site

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/24/2018 10:36 AM
Gravatar Much obliged for sharing us about this upgrade. Trust you won't get tired on making posts as useful as this. Improve Your Score Now

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/27/2018 12:46 AM
Gravatar World's most beautiful sit-stand desks,Adjustable Height Stand Up Desk. best standing desk

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/27/2018 9:53 AM
Gravatar I am certainly making the most of your site. You certainly have some incredible knowledge and extraordinary stories. the commercial storage facility

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 2/28/2018 9:29 AM
Gravatar My Essay Writer is the go-to source for the completion of academic assignments. If you are screaming out “Write my essay for me,” you’ve come to the most elite essay writing service. write my paper

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 3/1/2018 10:15 AM
Gravatar Amazing! This could be a standout amongst the most helpful web journals we have ever go over on thesubject. Really great data! I'm likewise a specialist in this point so I can comprehend your exertion. akyarbalik.web.tr

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 3/1/2018 10:15 AM
Gravatar A debt of gratitude is in order for sharing the data, keep doing awesome... I truly delighted in investigating your site. great asset... casinodunyasipro.com

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 3/1/2018 11:47 PM
Gravatar I have perused your article, it is exceptionally instructive and accommodating for me.I appreciate the significant data you offer in your articles. Much obliged for posting it.. bitcoin bahis siteleri

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 3/2/2018 6:44 AM
Gravatar I might want to thank you for the endeavors you have made in composing this article. I am trusting the same best work from you later on too.. Buy Research Chemicals Online

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 3/13/2018 10:43 AM
Gravatar I have perused your website it is exceptionally useful for me. I need to express profound gratitude to you. I have bookmark your site for future overhauls. pizza galata

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 3/13/2018 10:43 AM
Gravatar Great post yet I was thinking about whether you could compose a litte more on this subject? I'd be extremely grateful on the off chance that you could expand a tiny bit further. Value it! yenigundersanesi.web.tr

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 3/13/2018 10:43 AM
Gravatar A debt of gratitude is in order for this extraordinary post, i discover it extremely intriguing and exceptionally well thoroughly considered and set up together. I anticipate perusing your work later on. radahukuk.web.tr

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 3/15/2018 3:28 PM
Gravatar This site is truly incredible. The data here will unquestionably be of some assistance to me. Much obliged!. qbar kadikoy

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 3/15/2018 3:29 PM
Gravatar This is exceptionally instructive substance and composed well for a change. It's pleasant to see that a few individuals still see how to compose a quality post! ozgenindunyasi.web.tr

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 3/16/2018 3:39 AM
Gravatar If it's not too much trouble let me know in case you're searching for an article essayist for your site. You have some truly extraordinary posts and I feel I would be a decent resource. In the event that you ever need to take a percentage of the heap off, I'd completely love to compose some material for your web journal in return for a connection back to mine. If it's not too much trouble send me an email if intrigued. Much obliged to you! Joslevine casino tips

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 3/16/2018 3:39 AM
Gravatar I got what you mean , much obliged for posting .Woh I am upbeat to discover this site through google. en iyi poker siteleri

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 3/20/2018 1:34 AM
Gravatar Thanks for your write-up. What I want to point out is that while looking for a good internet electronics shop, look for a website with comprehensive information on critical indicators such as the personal privacy statement, security details, any payment procedures, and other terms in addition to policies. Constantly take time to look into the help along with FAQ areas to get a far better idea of how a shop functions, what they are capable of doing for you, and just how you can make use of the features. cut comcast bill

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 3/22/2018 11:51 AM
Gravatar "Satta Matka is the best site where you can get fastest Satta Matka results, Matka Boss, Matka Charts, free Kalyan Matka tips and Mumbai Matka tips with 100% fix Matka number from Satta Matka. Recover every kind of loss from Kalyan Matka, Milan Day, Milan Night, Rajdhani Day, Rajdhani Night Matka by SattaMatka .Buzz, All Satta Market's Live results are available with us and we provide fix Satta Matka Numbers. We are the most applauded website for offering the Satta Matka Live updates at the earliest.
For updates on live and the fastest Satta Matka results, upfates for Time Bazar, Madhur Matka. Milan Matka, Rajdhani Matka, our platform will give you that.
We are the most popular online Satta Matka website that provides the fastest Satta Matka results and Satta Matka number. Stay in touch with our website for the fastest Kalyan Matka or Satta Matka Tips. This is the worlds most famous Matka website loved by people engaged in Satta Bazar in Mumbai Matka. We SattaMatka.Buzz Provide Faster results than any other matka site with free fix satta matka lucky numbers. Please Bookmark Our Link https://sattamatka.buzz to get our daily updates visit us" matka

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 3/23/2018 6:25 AM
Gravatar A debt of gratitude is in order for sharing this data. I truly like your blog entry all that much. You have truly shared a useful and intriguing blog entry with individuals.. www.hatteringmagpie.com

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 3/26/2018 12:52 AM
Gravatar I respect this article for the all around explored substance and fantastic wording. I got so included in this material that I couldn't quit perusing. I am inspired with your work and expertise. Much obliged to you to such an extent. gelinlikkizlar.web.tr

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 3/26/2018 12:52 AM
Gravatar Howdy! Pleasant post! If you don't mind let us know when I will see a postliminary! twitch ile para kazan

# re: Add Column Descriptions in SQL Server

Left by nao consigo manter a ereçao por at 3/26/2018 4:27 AM
Gravatar Starting at the present moment, I have an undertaking that I have to finish. It will require a lot of investment to finish that task and that is the reason I require some impressive data. I am happy to report that the data here was incredible. nao consigo manter a ereçao por muito tempo

# re: Add Column Descriptions in SQL Server

Left by Robinjack at 4/27/2018 8:50 AM
Gravatar Thanks, I have been hunting for information about this subject for ages and yours is the best I have discovered so far. wormax.io unblocked

# re: Add Column Descriptions in SQL Server

Left by Misty at 5/10/2018 7:53 PM
Gravatar i am really very happy to read this awesome article, thanks to write for us.tcc live rate today

# re: Add Column Descriptions in SQL Server

Left by happywheelsgame at 5/14/2018 12:51 AM
Gravatar I was born in united states but grew up in New York.
happy wheels, These are probably my two favorite games of all time, and I'm trying my best to make it in the top 1-3 of google.
geometry dash

# re: Add Column Descriptions in SQL Server

Left by Raushan at 6/5/2018 12:58 PM
Gravatar Amazing Website For Satta Matka
That Can help You To Find Best Satta matka Datefix Game For Kalyan and Main Mumbai

Your comment:





 

Copyright © Timothy Klenke

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski