Post reply

Data Dictionary from within SQL Server 2000

  • July 17, 2003 at 1:49 pm

    #465924

    Great arcticle Mindy!

    This was my first use of my SQL Server Central membership and it has been extremely valuable!

    I am building a data dictionary for a SQL Server Database and this really "Got Me Off The Ground" so to speak.

    I also borrowed code from bnordber to put the columns I wanted into the final product.

    Thanks A Million!

  • January 29, 2004 at 2:27 pm

    #492263

    Great article!

  • February 4, 2005 at 6:24 am

    #540660

    It is possible to use sp_MSforeachtable in this context. You just need to strip out all the extraneous characters when you pass the name to the function call. For example, contrast the output from these two statements:

    -- output as [dbo].[tablename]

    exec sp_MSforeachtable 'print ''?'''

    -- output as tablename

    exec sp_MSforeachtable 'print replace(replace(''?'',''[dbo].['',''''),'']'','''')'


    Tony

  • February 4, 2005 at 7:42 am

    #540678

    Great article!

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • February 4, 2005 at 12:04 pm

    #540755

    Great article, but I'm thinking this is only the beginning.  Setting and querying the data is a start, but I would love to see a full solution where a script takes the data dictionary from SQL Server and converts it to a formatted Word Document.  Has anyone done something like this already?

  • February 4, 2005 at 1:38 pm

    #540773

    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

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

  • February 9, 2005 at 10:11 am

    #541395

    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

  • February 18, 2005 at 11:31 pm

    #543217

    Nice  article and usefull way to get information about tables

  • March 10, 2005 at 6:38 pm

    #546516

    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

  • March 11, 2005 at 9:03 am

    #546659

    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

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

  • June 17, 2005 at 4:04 am

    #566345

    Its fantstic option to make data dictionary into database itself.

    Thanks,

    Kiran Patil

     

  • February 6, 2006 at 9:29 am

    #619006

    Here is another illastration of a solution for your consideration;

    EXECUTE sp_MSforeachtable 'PRINT Object_Name(Object_ID(''?''))'

  • May 18, 2006 at 3:15 pm

    #638879

    nice option

  • October 27, 2006 at 5:12 am

    #668446

    SqlSpec uses these extended properties to generate a very comprehensive data dictionary. Link to it is in my sig below.

    ---------------------------------------
    elsasoft.org

  • August 1, 2008 at 5:49 am

    #851792

    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

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply