March 17, 2011 at 5:24 pm
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.
March 17, 2011 at 10:08 pm
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
Change is inevitable... Change for the better is not.
March 18, 2011 at 8:35 am
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? 🙂
March 19, 2011 at 11:06 am
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
March 19, 2011 at 11:14 am
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
March 21, 2011 at 10:35 pm
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?
March 22, 2011 at 2:18 am
Hi Danjam,
You are, of course, quite right about indexed views and standard version - thanks for correcting this.
Adam
March 22, 2011 at 2:20 am
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
March 22, 2011 at 2:16 pm
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.
September 4, 2012 at 10:16 am
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??
September 26, 2013 at 11:48 pm
I love extended properties. Very comprehensive introductory article!
The only think I find annoying about them (unless I missed something) is that you can't update them. Need to delete then add.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 27, 2013 at 12:54 am
Please note that the size of value of the extended property (data type sql_variant, with a default of NULL) cannot be more than 7,500 bytes.
Even with such limitation you could craft a reasonably large xml value (if you need one)...
September 27, 2013 at 2:03 am
I used Extended properties to try and keep track of the multiple databases across a range of servers as well as any database users. This way I can find out if a user is part of other databases and their permissions. I use the following script to quickly add information once the database is configured. This has been invaluable when an application has to be upgraded or the server decommissioned and I've need to find out exactly what databases are used by an application or server. :
USE [<databaseName>]
-- Database Details
EXEC sp_addextendedproperty @name=N'MS_Contact', @value=N'<business_owner_of_the_database>'
EXEC sp_addextendedproperty @name=N'MS_Created', @value=N'date';
EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'<description_of_the_appliaction_using_the_database>'
EXEC sp_addextendedproperty @name=N'MS_ApplicationServer', @value=N'<Application_server>'
GO
-- Database User details
declare @username as nvarchar(128), @userDesc as nvarchar(500), @permissionDesc as nvarchar(500), @dname as nvarchar(50);
select @dname = db_name();
set @username = '<database_user>'
set @userDesc = 'Application user of the <application>'
set @permissionDesc = 'Dbowner of ' + @dname + ' DB';
EXEC sys.sp_addextendedproperty @name=N'MS_contact', @value=N'<contact_for_the_user>' , @level0type=N'USER',@level0name=@username
EXEC sys.sp_addextendedproperty @name=N'MS_Created', @value='date' , @level0type=N'USER',@level0name=@username
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@userDesc , @level0type=N'USER',@level0name=@username
EXEC sys.sp_addextendedproperty @name=N'MS_Permissions', @value=@permissionDesc , @level0type=N'USER',@level0name=@username
GO
Example:
USE [SharePoint_ContentDB]
-- database
EXEC sp_addextendedproperty @name=N'MS_Contact', @value=N'John Smith, Infrastructure Team, ext 1234'
EXEC sp_addextendedproperty @name=N'MS_Created', @value='01/02/2013'
EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'SharePoint 2013 User Content Database'
EXEC sp_addextendedproperty @name=N'MS_ApplicationServer', @value=N'SharePoint2013 - 10.4.12.123'
GO
-- Database User details
declare @username as nvarchar(128), @userDesc as nvarchar(500), @permissionDesc as nvarchar(500), @dname as nvarchar(50);
select @dname = db_name();
set @username = 'DLR\svc_SHarePoint2013'
set @userDesc = 'Application user of the SharePoint 2013 database'
set @permissionDesc = 'Dbowner of ' + @dname + ' DB. Also DBOwner of all other SharePoint databases.';
EXEC sys.sp_addextendedproperty @name=N'MS_contact', @value=N'John Smith, Infrastructure Team, ext 1234' , @level0type=N'USER',@level0name=@username
EXEC sys.sp_addextendedproperty @name=N'MS_Created', @value='01/02/2013' , @level0type=N'USER',@level0name=@username
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@userDesc , @level0type=N'USER',@level0name=@username
EXEC sys.sp_addextendedproperty @name=N'MS_Permissions', @value=@permissionDesc , @level0type=N'USER',@level0name=@username
GO
September 27, 2013 at 2:08 am
dwain.c (9/26/2013)
I love extended properties. Very comprehensive introductory article!The only think I find annoying about them (unless I missed something) is that you can't update them. Need to delete then add.
You can use the following updateextendedproperty procedure:
EXEC databaseName.sys.sp_updateextendedproperty @name=N'PropertyName', @value=N'Value'
You just have to make sure the @name property matches the existing property name.
September 27, 2013 at 2:19 am
Maddave (9/27/2013)
dwain.c (9/26/2013)
I love extended properties. Very comprehensive introductory article!The only think I find annoying about them (unless I missed something) is that you can't update them. Need to delete then add.
You can use the following updateextendedproperty procedure:
EXEC databaseName.sys.sp_updateextendedproperty @name=N'PropertyName', @value=N'Value'You just have to make sure the @name property matches the existing property name.
I realize now that the issue was you didn't immediately know whether to use add or update unless you checked first. What is needed is a merge!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 31 through 45 (of 49 total)
You must be logged in to reply to this topic. Login to reply