LinkedIn | FaceBook | Twitter
You can also set these Extended Properties with code. I created a Template entry (under Change Tracking) by pressing CTRL-ALT-T in SQL Server Management Studio and then right-clicking that heading and adding a new template. Then I used the <> symbols to create replaceable parameters that I could change by pressing CTL-SHIFT-M. I thought I might share that here.
You don't have to use this as a template - you can just open it up, copy out the object you want to annotate, and then use the CTRL-SHIFT-M trick to fill in the parts you want. Make sure you are "In" the context of the database you're annotating (use the USE databasename; syntax for that) and as always, try this out on a test system thoroughly to make sure you know what you're doing. In the comments of the script I include the Books Online reference for the feature - make sure you look that over.
At the bottom section of this script is the code to read the properties once you've set them. Once again, set the context of the database you want and use the CTRL-SHIFT-M keystroke to set the values. Comments, kudos, whines, complaints and rants are welcome:
/* Name: None
Purpose: Create and Read Extended Properties Script
Author: Buck Woody
5/18/2009 - Initial Creation
Instructions: Open this script (or save it as a template entry), copy out the specific items
you want to annotate, and replace the parameters you want by pressing CTRL-SHIFT-M.
You can use the bottom section to read them. As always, run on a test system so that
you know what you're foing - FIRST.
Requires: SQL Server
Books Online Reference Link:
Notes: you can replace the "SCHEMA" in level0type with one of these,
if that is your desired scope. I'm only using SCHEMA objects like tables, views
or Stored Procedures:
-- EVENT NOTIFICATION
-- MESSAGE TYPE
-- PARTITION FUNCTION
-- PARTITION SCHEME
-- REMOTE SERVICE BINDING
You can replace the level1type with one of these, if you want to annotate them.
I normally only annotate a table, view, proc or function:
-- LOGICAL FILE NAME
-- XML SCHEMA COLLECTION
You can replac the level2type with one of these, if you want to annotate them,
I normally only annotate a COLUMN, CONSTRAINT, INDEX, PARAMETER or TRIGGER
/* Database */
@name = N'<Database_property_name, sysname, Property name>'
/* Schema */
/* Table */
/* Column */
/* Constraint */
/* Index */
/* Trigger */
/* View */
/* Stored Procedure */
/* Function */
/* Read the properties */
/* Schemas */
/* Tables */
/* Columns */
, objname AS 'Column'
, VALUE AS 'Description'
/* End Script */
PingBack from http://microsoft-sharepoint.simplynetdev.com/a-template-for-setting-and-reading-extended-properties/
Thanks for this, Buck! I've been trying to figure out some of this by trial and error, was painful.