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

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 youI'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.|||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'|||

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

|||

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?

|||

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/

|||Views are table too :) But yeah, I should have mentioned that too.|||

What is INFORMATION_SCHEMA ? Is it like a class ? As we are putting INFORMATION_SCHEMA.Table,etc.....

Can anyone explain ?

Thanks.

|||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".|||

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/