1

I am intending to use extended properties for documentation. So, to save descriptions and comments to tables, views, etc. Like this:

EXECUTE sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'custom description text', 
    @level0type = N'SCHEMA', @level0name = N'SchemaName', 
    @level1type = N'VIEW', @level1name = N'ViewName';

I just wonder if this is a good idea. Is this approach commonly used, and will the information stay in the database?

So far, I found that the information stays on ALTER VIEW, but it is deleted on DROP VIEW. That's OK. But is there any other possibility that the information might be deleted ?

SQL Police
  • 4,127
  • 1
  • 25
  • 54
  • `sp_dropextendedproperty` ? – Devart Jan 27 '16 at 13:35
  • @Devart yeah, well, ok ;) - I mean, is it possible that the server does "clean up" some properties by itself for some reason? I know the properties are stored in the database itself, in `sys.sysxprops`. I just want to be sure that they will stay there. – SQL Police Jan 27 '16 at 13:38

1 Answers1

2

Yes, it's a common approach used. Yes, it will stay in the database. The only real issue I've seen with it is when people create deployment scripts or refresh an environment and they don't script out the extended properties. Say for example if your deployment scripts decide to drop and recreate stored procs every deployment. Most of the script generation tools support scripting the extended properties, but a few have it disabled by default. http://www.csvreader.com/posts/data_dictionary.php

Bruce Dunwiddie
  • 2,888
  • 1
  • 15
  • 20
  • Thank you, this is good to hear, really reassuring me. Yes, of course, the properties need to be scripted upon move/deploy. But then, the extended properties open up a lot of good possibilities for automated documentation. Also thanks for the link, this csvreader looks like an interesting tool! – SQL Police Jan 27 '16 at 13:44