Req: Queries to return complete DB schema

  • 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

  • This was removed by the editor as SPAM

  • What are the info schema views lacking?

  • Steve,

    I can't provide an exhaustive list of what missing but take INFORMATION_SCHEMA.TABLES for example. It doesn't return the ID of each table/view returned so, you must uses:

    select *, OBJECT_ID(TABLE_NAME) as ID from INFORMATION_SCHEMA.TABLES= 0

    And too filter out all the Views returned and just have user tables (non-system or MS tables) returned, I use:

    select *, OBJECT_ID(TABLE_NAME) as ID from INFORMATION_SCHEMA.TABLES

    where OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0

    and OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsTable') = 1

    and OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsSystemTable') = 0

    and OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableIsFake') = 0

    The excessive number of calls to OBJECT_ID really hits performance. INFORMATION_SCHEMA views are a part of the SQL 92 standard and haven't evolved to cater for newer stuff added since it's release.

    The above query doesn't even return any information about full text catalog and index associated with each table either.

    Hope this helps,

    Ben

  • Go to the Master DB and look at the INFORMATION_SCHEMA views.  Alter whatever you need to and save the query as YourCompany_SCHEMA.

    Now you have whatever you need.  If needed, move the View to other DBs using the Import/Export Tool.

  • I've tried doing this via Enterprise Manager and alway get errors. Have you managed to do this successfully?

     

    Ben

  • Can you view the INFORMATION_SCHEMA views

  • I can view them in the treeview on the left of Enterprise Manager fine, but double click on any of them (TABLES) and get the following message:

    "Error 8181: Text for 'syscomments' is missing from syscomments. The object must be dropped and re-created before it can be used.

    Line 1: Incorrect syntax near 'AS'.

    Location:  colnames.cpp:4212

    Expression:  ptrCC

    SPID:  51

    Process ID: 1548

    Description: Bad error handling downstream of PtrGetCmd!!

    "

    When observing via Profiler, with the following query:

    select * from INFORMATION_SCHEMA.TABLES

    it simply shows that query.

    Any ideas,

    Ben

  • Do you have sysadmin priviliges?  If not, as someone with sysadmin to try.

     

    Also, try this... although I don't think it will work.

    Use Master

    EXEC sp_Helptext 'Information_Schema.Tables'

  • I'm sure I do have sysadmin priveleges. How do I tell?

    I use Windows authentication and my user profile has Admin priveleges system wide.

    Tried your suggestions. Get the follwoing error in Query Analyzer:

    "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).

    Server: Msg 11, Level 16, State 1, Line 0

    General network error. Check your network documentation.

    Connection Broken"

    This is odd because everythings running on a single desktop machine!

    Ben

  • You've got me stumped... but that doesn't take much.  Hopefully someone with more knowledge that I will help you.

    Good luck and sorry I couldn't help.

  • Well thanks for the help anyway, I appreciate it.

    Ben

  • Can you publish the exact contents of this queries / views that you are aiming to have in this forum?

    This would help, if anyone has got a query written for simmilar purpose. I also have my own set of stored procedures / functions which I use to query table metadata.

    So knowing the requirements / idea would certainly help.

     

    -- Amit

     


    -- Amit



    "There is no 'patch' for stupidity."



    Download the Updated SQL Server 2005 Books Online.

  • Amit,

    Please refer to my post at the top of the page on the same topic at:

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=98818&p=2

    I'm still compiling exact functional requirements, although in short I would describe this as:

    "the ability to retrieve complete schema and metadata information regarding every object type in a given Sql Server 7/2000 database"

    My complete list of objects are as follows:

    Check Constraint

    Column

    Database

    Default

    File

    Extended Property

    File Group

    Key

    Full Text Catalog

    Index

    Permission

    Role

    Rule

    Stored Procedure

    Table

    Trigger

    User

    User Defined Datatype

    User Defined Function

    View

    If there's anything I've missed, please let me know. For each of these objects, I want to build queries that would retrieve ALL known flat (non-relational) meta-data information.

    For example, Tables requires ID, Name, CreationDate, Owner, FullTextCatalogName, FullTextIndex, FileGroup, ClusteredIndex, PrimaryKey, IsAnsiNullsOn, IsQuotedIdentOn etc etc. Relational meta-information such as columns, indexes, triggers etc will be extracted with different queries and attributed to their parent object in code.

    Thanks

    Ben

  • As per your requirements, Tables requires ID, Name, CreationDate, Owner, FullTextCatalogName, FullTextIndex, FileGroup, ClusteredIndex, PrimaryKey, IsAnsiNullsOn, IsQuotedIdentOn

    Just to check, You are thnking of a queries such as

    select ID, Name, CreationDate, Owner, OwnerId, FullTextCatalogName, HasFullTextIndex, FileGroupName , HasClusteredIndex, HasPrimaryKey, HasIsAnsiNullsOn, HasIsQuotedIdentOn  from TABLEINFO

     

    Which would give you all information about a table in a single row.

     

    -- Amit


    -- Amit



    "There is no 'patch' for stupidity."



    Download the Updated SQL Server 2005 Books Online.

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

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