missing tables in sysobjects

[neo]

Hi,
I'm using "select [name] from sysobjects where OBJECTPROPERTY( id,
N'IsUserTable') = 1" to get all user-defined tables(about 17000
tables)
and found out there are tables missing in sysobjects.
How can I refresh/rebuild sysobjects table?
is there a better way to get a list of user-defined tables?

thanks,

[Mystery Man]

Re: missing tables in sysobjects

second714@hotma il.com (neo) wrote in message news:<155f8e7d. 0401061038.11ea 8cc7@posting.go ogle.com>...[color=blue]
> Hi,
> I'm using "select [name] from sysobjects where OBJECTPROPERTY( id,
> N'IsUserTable') = 1" to get all user-defined tables(about 17000
> tables)
> and found out there are tables missing in sysobjects.
> How can I refresh/rebuild sysobjects table?
> is there a better way to get a list of user-defined tables?
>
> thanks,[/color]

Perhaps what is missing are VIEWS?

Try the following

select * from INFORMATION_SCH EMA.tables

Please let me know how you get on.

[Erland Sommarskog]

Re: missing tables in sysobjects

neo (second714@hotm ail.com) writes:[color=blue]
> I'm using "select [name] from sysobjects where OBJECTPROPERTY( id,
> N'IsUserTable') = 1" to get all user-defined tables(about 17000
> tables)
> and found out there are tables missing in sysobjects.
> How can I refresh/rebuild sysobjects table?
> is there a better way to get a list of user-defined tables?[/color]

So how to you know they are missing? I'd say that if a table is not in
sysobjects it does not exist...

You can use DBCC CHECKCATALOG to check the system tables for inconsistency.

Mystery Man's suggestion that you might be looking for views seems worth
investigating. I'll add table-valued functions to the list.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

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