Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Using Extended Properties To Keep Everyone Informed Expand / Collapse
Author
Message
Posted Wednesday, December 1, 2010 1:56 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:51 AM
Points: 585, Visits: 384
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.
Post #1028462
Posted Friday, December 3, 2010 4:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 19, 2013 2:00 PM
Points: 183, Visits: 479
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.

Post #1030202
Posted Saturday, December 4, 2010 3:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, December 4, 2010 3:47 AM
Points: 1, 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
Post #1030253
Posted Wednesday, April 25, 2012 10:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, April 6, 2013 8:07 PM
Points: 12, 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
Post #1290093
Posted Wednesday, January 1, 2014 2:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 11, 2014 12:51 PM
Points: 6, Visits: 32
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.
Post #1526956
Posted Thursday, January 2, 2014 11:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, 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.
Post #1527243
Posted Thursday, January 2, 2014 11:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, 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.
Post #1527246
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse