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 «««123

Data Dictionary from within SQL Server 2000 Expand / Collapse
Author
Message
Posted Friday, February 04, 2005 1:38 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 1,035, Visits: 408

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.




/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Post #159811
Posted Wednesday, February 09, 2005 10:11 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 07, 2007 8:30 AM
Points: 74, Visits: 1

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




Post #160594
Posted Friday, February 18, 2005 11:31 PM
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, January 03, 2013 12:52 PM
Points: 519, Visits: 26

Nice  article and usefull way to get information about tables




Post #162882
Posted Thursday, March 10, 2005 6:38 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 16, 2006 1:20 AM
Points: 92, Visits: 1

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.




"Work like you don't need the money;
dance like no one is watching;
sing like no one is listening;
love like you've never been hurt;
and live every day as if it were your last."
~ an old Irish proverb
Post #167062
Posted Friday, March 11, 2005 9:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 1,035, Visits: 408

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.




/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Post #167242
Posted Friday, June 17, 2005 4:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 08, 2014 5:02 AM
Points: 6, Visits: 362

Its fantstic option to make data dictionary into database itself.

Thanks,

Kiran Patil

 

Post #191585
Posted Monday, February 06, 2006 9:29 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, June 05, 2012 12:03 PM
Points: 117, Visits: 163
Here is another illastration of a solution for your consideration;

EXECUTE sp_MSforeachtable 'PRINT Object_Name(Object_ID(''?''))'
Post #256059
Posted Thursday, May 18, 2006 3:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 27, 2012 2:55 PM
Points: 22, Visits: 79

nice option

Post #281255
Posted Friday, October 27, 2006 5:12 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 18, 2013 4:06 PM
Points: 471, Visits: 589
SqlSpec uses these extended properties to generate a very comprehensive data dictionary. Link to it is in my sig below.

---------------------------------------
elsasoft.org
Post #318651
Posted Friday, August 01, 2008 5:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 01, 2008 5:46 AM
Points: 1, Visits: 0
Hi, I am looking to create a data dictionary straight from my sql tables and this article was great but i alson need to include the type and length of every fields. Is this possible? I am using sql 2000
Post #545032
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse