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,

second714@hotmail.com (neo) wrote in message news:<155f8e7d.0401061038.11ea8cc7@posting.google. com>...
> 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,

Perhaps what is missing are VIEWS?

Try the following

select * from INFORMATION_SCHEMA.tables

Please let me know how you get on.

neo (second714@hotmail.com) writes:
> 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?

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/techinf...2000/books.asp