locked
can't get the complete list of tables owned by the dbo RRS feed

  • Question

  • Hi,

    i am using sp_tables to get the list of the tables in the database owned by dbo

    The database i am trying to query would let me query all the tables present in the database.there are almost a 1000 of them(owned by dbo) .I can query them and i get the records.  

    But the execution of the stored procedure  "sp_tables"  would return only 10 or so  tables.I was puzzled by this.  

    can someone clarify this inconsistency.  


    thank you
    Monday, October 10, 2005 8:59 PM

Answers

  • I'm not sure what is causing the behavior you see, but you can get a list of tables owned by dbo by doing this:

    select * from sysobjects where type = 'U' and uid=1

    This query will work correctly when ported to SQL 2005.

    Monday, October 10, 2005 11:45 PM
  • Hi,

    As a purely practical matter, it is hard for me to imagine MSFT changing the dbo==1 relationship.  However, you make an excellent point - AFAIK the BOL does not make an explicit guarantee, and it is best not to rely on hardcoding ID#s into your scripts.

    Your query won't work on a case-sensitive server.  I recommend this as the simplest

    select * from sysobjects where type = 'U' and uid=user_id('dbo')

    Notice that I am looking for user-created tables (type = 'U').  The list you get back in SQL 2005 is different than the list in SQL 2000.

    Moreover, if you look for MSFT-created system objects (type='S')

    select * from sysobjects where type = 'S' and uid=user_id('dbo')

    You get back an empty set in SQL 2005 because all the system tables are now owned by the uid=user_id('sys').   Moreover, the system tables themselves are different in SQL 2005 than they are in SQL 2000.   In fact, they are not even directly SELECT-able anymore.  You must use the catalog views to query the catalog.

    Hope this helps

     

     

    Wednesday, April 5, 2006 1:45 AM
  • From BOL:

    Information Schema Views

    These views provide an internal, system table-independent view of the SQL Server meta data. Information schema views allow applications to work properly even though significant changes have been made to the system tables. The information schema views included in SQL Server conform to the SQL-92 Standard definition for the INFORMATION_SCHEMA.

    SQL Server supports a three-part naming convention when referring to the current server. The SQL-92 standard also supports a three-part naming convention. However, the names used in both naming conventions are different. These views are defined in a special schema named INFORMATION_SCHEMA, which is contained in each database. Each INFORMATION_SCHEMA view contains meta data for all data objects stored in that particular database

     

    This table describes the relationships between the SQL Server names and the SQL-92-standard names.

    SQL Server name Maps to this equivalent SQL-92 name
    Database catalog
    Owner schema
    Object object
    user-defined data type domain

    This naming convention mapping applies to these SQL Server SQL-92-compatible views. These views are defined in a special schema named INFORMATION_SCHEMA, which is contained in each database. Each INFORMATION_SCHEMA view contains meta data for all data objects stored in that particular database.

     

    Denis the SQL Menace

    http://sqlservercode.blogspot.com/

     

    Friday, April 14, 2006 11:59 AM

All replies

  • I'm not sure what is causing the behavior you see, but you can get a list of tables owned by dbo by doing this:

    select * from sysobjects where type = 'U' and uid=1

    This query will work correctly when ported to SQL 2005.

    Monday, October 10, 2005 11:45 PM
  • Is dbo always 1???

    isn't it safer to do:

    select o.name from sysobjects o inner join sysusers u on o.uid = u.uid
    where o.xtype = 'u' and u.name = 'dbo'
    Tuesday, October 11, 2005 3:49 AM
  • Hi,

    As a purely practical matter, it is hard for me to imagine MSFT changing the dbo==1 relationship.  However, you make an excellent point - AFAIK the BOL does not make an explicit guarantee, and it is best not to rely on hardcoding ID#s into your scripts.

    Your query won't work on a case-sensitive server.  I recommend this as the simplest

    select * from sysobjects where type = 'U' and uid=user_id('dbo')

    Notice that I am looking for user-created tables (type = 'U').  The list you get back in SQL 2005 is different than the list in SQL 2000.

    Moreover, if you look for MSFT-created system objects (type='S')

    select * from sysobjects where type = 'S' and uid=user_id('dbo')

    You get back an empty set in SQL 2005 because all the system tables are now owned by the uid=user_id('sys').   Moreover, the system tables themselves are different in SQL 2005 than they are in SQL 2000.   In fact, they are not even directly SELECT-able anymore.  You must use the catalog views to query the catalog.

    Hope this helps

     

     

    Wednesday, April 5, 2006 1:45 AM
  • Would the best advice be to use the INFORMATION_SCHEMA.tables view?

    select *
    from INFORMATION_SCHEMA.TABLES
    where TABLE_SCHEMA = 'dbo'

    Most people shouldn't be looking up system tables like this anyhow, and this syntax works in 2000 and 2005 (7.0?  can't remember) and should work in the next version, and the next, etc...

    Moreover, the system tables themselves are different in SQL 2005 than they are in SQL 2000.   In fact, they are not even directly SELECT-able anymore.  You must use the catalog views to query the catalog.

    I just wonder how stable these views will be in the future?  I don't plan to use them in production code for anything.  (other than monitoring, of course)  The information_schema is supposed to be a stable/never subtracted from set of tables, right?

    Wednesday, April 5, 2006 4:42 AM
  • USE pubs
    GO

    SELECT * FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA ='dbo'
    AND TABLE_TYPE ='BASE TABLE'  --If you leave this out you will also get VIEWS

     

     

    Denis the SQL Menace

    http://sqlservercode.blogspot.com/

     

     

    Wednesday, April 5, 2006 7:43 PM
  • Views are table too :)  But yeah, I should have mentioned that too.
    Wednesday, April 5, 2006 7:45 PM
  • What is INFORMATION_SCHEMA ? Is it like a class ??  As we are putting INFORMATION_SCHEMA.Table,etc.....

     

    Can anyone explain ?

     

    Thanks.

    Thursday, April 13, 2006 6:20 AM
  • It is a special schema (in 2000 owner) used to contain metadata that is standard across platforms. It is kind of like DBO, but it is "special".
    Thursday, April 13, 2006 5:30 PM
  • From BOL:

    Information Schema Views

    These views provide an internal, system table-independent view of the SQL Server meta data. Information schema views allow applications to work properly even though significant changes have been made to the system tables. The information schema views included in SQL Server conform to the SQL-92 Standard definition for the INFORMATION_SCHEMA.

    SQL Server supports a three-part naming convention when referring to the current server. The SQL-92 standard also supports a three-part naming convention. However, the names used in both naming conventions are different. These views are defined in a special schema named INFORMATION_SCHEMA, which is contained in each database. Each INFORMATION_SCHEMA view contains meta data for all data objects stored in that particular database

     

    This table describes the relationships between the SQL Server names and the SQL-92-standard names.

    SQL Server name Maps to this equivalent SQL-92 name
    Database catalog
    Owner schema
    Object object
    user-defined data type domain

    This naming convention mapping applies to these SQL Server SQL-92-compatible views. These views are defined in a special schema named INFORMATION_SCHEMA, which is contained in each database. Each INFORMATION_SCHEMA view contains meta data for all data objects stored in that particular database.

     

    Denis the SQL Menace

    http://sqlservercode.blogspot.com/

     

    Friday, April 14, 2006 11:59 AM