SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Extended Properties Introduction


Extended Properties Introduction

Author
Message
Vedran Kesegic
Vedran Kesegic
Right there with Babe
Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)

Group: General Forum Members
Points: 732 Visits: 1266
I heard (from a person closest to the author) that in one of the next releases of XDetails plugin for Sql Management Studio it will be possible to easily edit column comments. It will be editing in-line in grid that displays descriptions near the table columns' names and types.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89437 Visits: 41144
Adam Aspin (3/17/2011)
Thanks Jeff - praise from a leading light like yourself makes my day!
There are three more articles to come (one a week for 3 weeks) which, hopefully, will help newbies not only add, but also extract and update this metadata in a comprehensive manner.
Adam


I saw that in your intro and I'm very much looking forward to them.

And, thank you for the very kind words. Blush

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Patrick2525
Patrick2525
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 180
ACinKC (3/17/2011)

I created an Excel/VBA solution for my current gig that read in the metadata for the tables and columns into a workbook (one worksheet per table) [...] It's a two-way tool, creating the Data Dictionary (in Excel) from the database and using the same tool in turn to update the EP values in the database.


Sounds interesting... is that something you can share? :-)
JediSQL
JediSQL
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 189
This regards the use of the MS_ prefix. SQL Server Management Studio and the third party "SQL Documentation" tools I have demoed use the MS_Description as the name/property that they display. So I have set up our extended property system to use MS_Description for our general description comments. That way, someone using SQL Server Management Studio or one of the third party tools will get our main description comment. But it is good to know that Microsoft uses other MS_* names/properties and, in general, the MS_ prefix should be avoided.

I went with Microsoft's lead on using MS_ as a prefix to set up a name space, so I have our team using our own prefix, CSIT_ (CureSearch Information Technology).

In our industry, cancer research, the NCI (National Cancer Institute) is essentially requiring all of its grantees to use its ISO-11179 meta data repository. Once you get used to it, it's pretty cool. We are setting it up so that all our columns, etc., will be assigned data element descriptions, and the assigning of data elements to columns will automatically update the MS_Description property for casual inspection.

Sincerely,
Daniel
JediSQL
JediSQL
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 189
This regards indexed views. Indexed views are available in standard edition. What is limited to the enterprise/developer edition with regards to indexed views is the query optimizer's use of indexed views to enhance performance of queries that do not explicitly reference an indexed view, but there is an indexed view that has some of the joins already computed.

Sincerely,
Daniel
aigl
aigl
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 36
I see great use of the Extended Properties if you can make use of them as tool tips in Reporting Services. Hopefully one of the future topics will cover that.

Is it possible for View columns to inherit the Extended Properties from the Table columns that the View is based on?
Adam Aspin
Adam Aspin
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 1032
Hi Danjam,
You are, of course, quite right about indexed views and standard version - thanks for correcting this.
Adam
Adam Aspin
Adam Aspin
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 1032
Hi Aigl,
I have never used extended properties in reporting services, so please share the knowledge if you get this working.
Also, as far as I know, you have to specify column view properties per view (or write the code to extract and attribute them) and there is no simple inheritance.
Regards,
Adam
Cade Roux
Cade Roux
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 491
aigl (3/21/2011)Is it possible for View columns to inherit the Extended Properties from the Table columns that the View is based on?


I don't think so, and I wasn't even able to apply extended properties to columns of views (although I was able to apply them to views themselves)

However, I see in this latest article, Adam has a query which is supposed to expose them, so I'm going to go revisit that.
Yitzchok Lavi
Yitzchok Lavi
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 217
Has anyone found documentation of the Extended Properties available in SQL Server 2012 to parallel that for 2005-2008R2 which is available at http://msdn.microsoft.com/en-us/library/ms190243.aspx??
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