Just a quick correction... The article stated "These values are stored as extended properties. The user can add as many extended properties as they like. They are stored in a hidden table that you will not see in any database on the SQL Server."
This isn't accurate, the extended properties are stored in sysproperties and I have found it very useful to be able to query this table directly without using the built-in functions.
I know that one of the cardinal rules is that you don't directly modify or query data in the system tables, but the functions that MS provides for this purpose are such a kludge that I entirely disregard this advice for extended properties.
You can replace the cursor at the end using the PARSENAME function as such:
exec sp_MSforeachtable N'declare @tbl sysname set @tbl = PARSENAME(''?'',1) exec get_column_details @tbl'
I never knew about this function until recently, and I have so many uses for it, just thought I would "append" the article and pass this little tidbit on.
Mindy
Nice article and usefull way to get information about tables
Hi there,
Really liked your article on Data Dictionary stuff.. I used your code first thing on a current project that I just took over design for and since there was NO documentation I really needed the "leg up"...
Question for anyone out there that is somewhat related to this topic:
Does anyone know the length and datatype (and any other goodies or details) of the 'Description Meta Data field' mentioned in this article (via EM)?
TIA.
Health in mind, body & soul,
Dolphin.
In my earlier post I pointed out that this data is NOT in a "hidden table" that is not accessible to the user. This information is in fact in sysproperties table and the column that contains the description is called [Value] with a datatype of sql_variant.
If you ask me (and I know you didn't), the desingn of this functionality is pathetic. Whoever came up with this hairbrained scheme obviously didn't know, or care, anything about good database design. You would almost be better off using an Excel spreadsheet to maintain your metadata.
I long ago gave up using the SQL server extended properties for documentation purposes. Neither ErWin or ER Studio reads or writes to the sysproperties table and since I have used both (currently ER Studio) why would I create extra work for myself?
If you don't use a capable modeling tool and want to keep your meta data in the database, I'd highly recommend that you "roll your own" to suit your needs. I have done this on occasion and it has proven to be a more workable solution than dealing with the extended properties nonsense.
Its fantstic option to make data dictionary into database itself.
Thanks,
Kiran Patil
nice option
