Post reply

Queries for complete DB schema?

  • February 6, 2004 at 1:24 pm

    #493156

    I have no idea

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • February 6, 2004 at 6:08 pm

    #493183

    Comment:

    good luck, and I mean it!  It would be very useful to be able to script out an object-model of the db schema without any stupid manual steps.  I hope you plan on posting your script when you're done.

    As an caution, anything that references system tables should be isolated from the rest of your code as much as possible, preferably in functions.  Microsoft does not support querying the system tables and could change them at any time.

    I would guess that one good way to store the output data would be in an XML schema...what were you planning on being able to use to display the hierarchal data?

    Signature is NULL

  • February 7, 2004 at 4:11 am

    #493189

    Calvin,

    I'm aware that the system tables are subject to change without notice and will have to monitor service pack changes. Is there a way to programmatically obtain service pack information from the database engine? Maybe a registry setting or something?

    I'm looking at storing captured schema as XML as it's easy for a developer to manipulate, and to be honest, proprietry formats are a scourge of programming. However, the context in which my client wishes to use my findings might conflict with this. We'll have to see.

    I can't comment on the display of the captured schema, but there's many ways to display hierarchical data, treeviews probably the best and most user friendly.

     

    Thanks

    Ben

  • February 9, 2004 at 12:55 pm

    #493297

    Ben,

    I use @@version from the tSQL command line ("select @@version").  I have to parse the string, but it's not to bad and is backwardly compatible.

    I wouldn't know how to do it from the windowns cmd line, but that would probably be a registry lookup.

    Once the schema is in an XML format it could be displayed in a number of different ways, I guess.   Useful stuff for sure.

    cl

     

    Signature is NULL

  • February 10, 2004 at 2:37 am

    #493338

    Thanks Calvin, that's well handy!

     

    Ben

Viewing 5 posts - 16 through 19 (of 19 total)

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