Post reply

Data dictionary

  • September 9, 2003 at 9:18 am

    #83166

    Is there any way to generate a data dictionary using SQL Server? If not, could you kindly sugguest some good data dictionary software? Thanks.

    -sunrise

  • September 9, 2003 at 10:03 am

    #473253

    This is always a little kludge and I haven't seen any great ways to do it. Embarkadero's ER/Studio would do some as would one of FMS Software's products. Not sure I like them, but they will get you started.

    http://www.embarcadero.com/products/erstudio/index.asp

    http://www.fmsinc.com/Products/sqlanalyzer/index.html

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    http://www.dkranch.net

  • September 10, 2003 at 7:06 am

    #473405

    We use Innovasys Document! X. Only thing it can't pull out is the diagrams in the database. Also works for COM components.

    Patrick T Ward

  • September 10, 2003 at 8:38 am

    #473438

    I gave up on ER Studio as it would not reread changes to the database and was a nightmare to maintain. Instead I use SQL Servers extended properties for tables, views and stored procedures, then the details in EM's database designer. Then use a query like below with a custom stored procedure that I can send you if you like. I then add on some select statements from any lookup tables whos values I want in the dictionary and walah. All that data reside in the database, and If I want to include lookup values in my dictionary, I don't have to maintain them outside of the db.

    --First select all extended properties at a table level

    --this returns all extended properties for user tables (where extended properties exist, so ensure all tables to be documented say something

    -- unless you want them hidden, then don't). To select an individual table replace the 3rd null from the right to the table name.

    select *

    from ::fn_listextendedproperty (null, 'user', 'dbo', 'table', null, null, default)

    -- then get the properties, description, field types, lengths, nullability ... out of enterprise manager

    -- for all tables. Could also run through each table extended properties using

    --select *

    --from ::fn_listextendedproperty (null, 'user', 'dbo', 'table', <tablename>, 'column', default)

    --but this doesn't seem as informative, but it holds multiple properties per column!?.

    exec DataDictionaryTableReport

  • September 10, 2003 at 2:16 pm

    #473538

    These guys have a freeware tool that creates a basic dictionary. Maybe it'll do for you.

    http://www.cobbinfosys.com/

Viewing 5 posts - 1 through 4 (of 4 total)

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