Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Extended Properties To Keep Everyone Informed


Using Extended Properties To Keep Everyone Informed

Author
Message
Rayven
Rayven
SSChasing Mays
SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)

Group: General Forum Members
Points: 627 Visits: 428
Great article.

I've been enforcing extended properties on database objects now for two years and it has made an incredible difference.

Tables and procedures are now well documented.

SSRS reports are available to the developers to allow them to see the documentation, and with the aid of ApexSQLDOC I can produce a full data dictionary on request.

Because we hold databases for multiple companies, all with almost the same structure, additional extended properties help identify who the database belongs to, who is responsible for it and contact details.

On top of this some were developed to be used to help in code rollout. Whilst the databases are 90% identical code, occasionally a procedure strays due to a fast hotfix. A quick change to a custom "SYNC" property allows us to alert developers that the code is different to other databases, and to DBAs to review the code ASAP to recode it and bring it back into line. This, with the aid of a few custom scripts, has decreased the rollout time of new projects from hours to a matter of minutes.

It was difficult to persuade other people to use, but now that it is firmly embedded in the culture here it has proved worth its weight in gold. They are definitely an underused and extremely useful recourse.


---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.

david_wendelken
david_wendelken
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 480
I use extended properties extensively in my own work as a developer.

I am loath to use them on other developer's work as a dba.

That's because when a script gets run that drops an oblect, the extended properties I added will be lost.

I'm not the only dba on the team, so I can't ensure that the extended properties are saved and restored after the item is created. Nor can I ensure that the developers add them to their source code.

Incidentally, extended properties are not included in the generated create scripts for all objects, i.e., views. That's particularly annoying to me as I like to include column definitions in the views for the data dictionary reports.
maxvigil2004
maxvigil2004
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 0
Eric B-295769 (11/29/2010)
Comments posted to this topic are about the item <A HREF="/articles/Central+Management+Server/71499/">Using Extended Properties To Keep Everyone Informed</A>


Hi,

I am facing the Erron 3709 while i open the Program
Z1024
Z1024
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 57
My web based SQL documentation tool - LiveDoco addresses (to an extent) some of the concerns raised here:
1) It allows easy access (adding/editing/searching) to any extended properties, including the default MS_Description
2) It can export them to a SQL file that can be used to merge(add new/update existing) them into a db with similar/same structure. This SQL file could be saved somewhere in case something happens to the DB (and/or all your EPs in it).

It can do other things as well, but not directly related to this topic so please see www.livedoco.com for more information if interested.



www.livedoco.com - your Live SQL Server database documentation tool
crusso-1157926
crusso-1157926
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 61
I had used the description extended property to keep a list of what all columns in my tables were used for and whether they were really being used anymore. I never thought of using it also to keep track of what stakeholders were interested in the database. Nor did I know that you could setup custom extended properties.

Thank you for sharing this information. Very much appreciated.
sneumersky
sneumersky
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2212 Visits: 487
Extended properties are indeed the glue that holds together the BUSINESS component of our "metadata repository".....as it is just one component of a bigger master data strategy. The biggest challenge is usually in keeping this information up to date.
sneumersky
sneumersky
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2212 Visits: 487
Also, the BIDSHELPER tool on CODEPLEX extracts out these extended properties from the relational database and lands them into an SSAS solution--a very convenient and time saving tool! However, this is a one-way data transfer as you cannot bring SSAS metadata into the RDBMS at this time.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search