Post reply

Queries for complete DB schema?

  • February 5, 2004 at 4:21 am

    #161610

    Hi,

    I have a need to create T-SQL queries that can be used to return complete and comprehensive schema information for all entities in a given database. In an ideal world this would cover version 7, 2000 and possbily Yukon.

    Does any one know if such queries exist in the public domain?

    I have used the in-built Information Schema Views and have looked at SQL-DMO but found both of these lacking somewhat.

     

    Many thanks

    Ben Fidge

    Senior Systems Developer

  • February 5, 2004 at 4:39 am

    #492861

    Does this help?

    http://qa.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=246

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

  • February 5, 2004 at 4:52 am

    #492862

    Frank,

    Thanks for the info. It's a good starting point although I'm trying to build something that is a lot more comprehensive.

     

    Thanks

    Ben

  • February 5, 2004 at 5:32 am

    #492867

    I'm sure you'll find what you need when searching through the script section.

     

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

  • February 5, 2004 at 10:23 pm

    #492993

    Enterprise manager Generate SQL Script is an option.

  • February 6, 2004 at 4:14 am

    #493021

    I'm aware of this, and thanks, but I need to be able to programmatically create a class hierarchy, akin to SQL-DMO, based on the results of individual queries. I feel that this is best done via queries to the system tables as opposed to parsing textual DDL statements.

    For example, I'd like to be able to query the relevant system tables to return every relevant detail concerning any given database entity, be it a table, stored procedure etc. This has to be detailed enough to potentially create an exact replica the queried database, although this isn't the task I'm trying to achieve, just the level of detail I require.

    So, for tables, I'll need id, name, owner, whether it's full-text indexed, the full-text catalog name and anything else relevant.

    Any ideas sincerely and greatfully received,

     

    Ben

     

  • February 6, 2004 at 4:23 am

    #493022

    What about utilizing the *.PROPERTY stuff?

     

    --
    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 4:34 am

    #493023

    Frank

    Take the following query:

     

    select *, OBJECT_ID(Table_Name) as ID 
    from information_schema.tables 
    where OBJECTPROPERTY(OBJECT_ID(Table_Name), N'IsTable') = 1
    and OBJECTPROPERTY(OBJECT_ID(Table_Name), N'IsMSShipped') = 0
    and OBJECTPROPERTY(OBJECT_ID(Table_Name), N'IsSystemTable') = 0
    and OBJECTPROPERTY(OBJECT_ID(Table_Name), N'TableIsFake') = 0
     
    The performance hit on this due to the number OBJECT_ID calls increases 
    the execution time by nearly 4 times that of a straight select * from information_schema.tables. 
    And that is without obtaining info about full text index etc.
     
    I appreciate your help with this BTW. It's good to have someone to bounce ideas off!
    Ben

  • February 6, 2004 at 6:00 am

    #493041

    Ben,

    I think you've misunderstood me.

    I agree that running the above query against INFORMATION_SCHEMA.TABLES is likely to suffer performance, because INFORMATION_SCHEMA.TABLES returns only user-defined objects so that

    where OBJECTPROPERTY(OBJECT_ID(Table_Name), N'IsTable') = 1
    and OBJECTPROPERTY(OBJECT_ID(Table_Name), N'IsMSShipped') = 0
    and OBJECTPROPERTY(OBJECT_ID(Table_Name), N'IsSystemTable') = 0
    and OBJECTPROPERTY(OBJECT_ID(Table_Name), N'TableIsFake') = 0

    don't really make sense.

    As you've wrote you want something comprehensive, I thought more of something like this:

    select *,

    OBJECTPROPERTY(id, N'IsTable'),

    OBJECTPROPERTY(id, N'IsMSShipped'),

    OBJECTPROPERTY(id, N'IsSystemTable'),

    OBJECTPROPERTY(id, N'TableIsFake')

    from sysobjects

    And as you are afraid of performance, what do you want this for?

    Documentation or some production code?

    --
    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:51 am

    #493051

    I REALLY like the script at

    http://qa.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=246

    Thanks, Frank.  I'm adding that to my toolkit.

  • February 6, 2004 at 7:42 am

    #493066

    Frank,

    Firstly, apologies if this response appears to be posted twice. I'm sure I sent a response earlier, but it doesn't appear to have been sent!

    I did indeed misread your earlier response, my mistake.

    I was under the asumption that INFORMATION_SCHEMA.TABLES returned user and systems tables. I will have a tinker with your alternative query, thanks.

    I'm researching for a client who requires the ability to programmatically retrieve the schema of very large databases. The detail required is such that it must be feasible to create an exact duplicate of any given database, although this isn't the actual task at hand. For obvious reasons I can't say too much about this. Needless too say that detail and performance are paramount.

    Really, I need to get a list of all objects supported by Sql Server, and then complete list of all properties, attributes etc supported by each object on each version. Then I can go about writing the queries to retrieve this information.

    I was hoping (maybe rather foolishly) that queries to retrieve all schema information about any given object would exist somewhere in the public domain, as I would imagine there's 101 different uses for such queries, and therefore quite a common task.

    Ben

  • February 6, 2004 at 7:56 am

    #493072

    That's indeed a huge task for you!

    I don't know for sure, but aren't there commercial products for this around?

    But I guess as you've mentioned the public domain, that the customers needs are unlimited, but budget very limited

    --
    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 8:08 am

    #493086

    Haha, I like that. Isn't it always the way.

    I have looked at one particular library to do this but their licensing conflicts with my client's long term interests.

    Looks like I'll have to spend a month with my head in Sql Books online!

     

    Thanks for all your help Frank.

     

    Ben

  • February 6, 2004 at 8:21 am

    #493090

    One last word.

    Before you're drowning in BOL this one might also be interesting

    http://www.microsoft.com/sql/techinfo/productdoc/2000/systables.asp

    And remember that directly querying the system tables is NOT supported by Microsoft, so you should carefully make use of such. They claim the right to change or modify system stuff at service pack level.

     

     

    --
    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 9:01 am

    #493098

    I think I've just found my new wallpaper for my office!!

    Thank you very much Frank. I was just looking at the system tables in BOL, and you're right, it would be easy to to drown.

    What is the notation used in the diagrams? UML?

     

    Ben

Viewing 15 posts - 1 through 15 (of 19 total)

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