sysobjects tables that don't exists in database

Question

sysobjects tables that don't exists in database

hansel.ortiz

Mr or Mrs. 500

Points: 585
More actions
April 23, 2010 at 1:51 pm

#96656

guys im having an issue.. im new administrating SQL SERVER 2000, my experience is most in SQL2005 and 2008, so the system structure of the SQL2005 is almost the same as the SQL 2000...
i was getting al tables from sysobjects to make an script to do an DBCC check Table, so in the
sysobjects table are objects (tables) that actually don't exists in the database..
what should i do to update the sysobjects, or the statistics.. i've been surfing the web to find something but i just get scripts to verify if the objects exists in the database wich i already did that..
all sugestions are accepted

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

hansel i'm not sure what you are doing...

sysobjects in SLQ 2000 contains the metadata of all the objects in the database, including the tables; you can join sysobjects to syscolumns to get the structure of tables or views;

so if it does not exist in sysobjects, it does not exist in the database, period. no exceptions, unless you are trying to salvage a seriously corrupt database.

what is it you think exists but is not in sysobjects ?

could you be referring to tables owned by a different owner? so bob.Orders is different from dbo.Orders?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

hansel.ortiz Mr or Mrs. 500 Points: 585 More actions · Answer 2

well.. i've been editing the Sp_tables to see the structure an make some joins to query all tables in a database..

i was doing a join from sysobjects to the real database to bring all the names of actual tables in the database using -->

select 'DBCC'+' '+'CHECKTABLE'+' '+'('+name+', noindex'+')'+' '+'with'+' '+'ALL_ERRORMSGS'+' ' from sysobjects where type='U'

this query output is like this "DBCC CHECKTABLE (spt_values, noindex) with ALL_ERRORMSGS "

BTW when i was editing sp_tables i saw the sysobjects and syscolumns, but i don't know to make the join between this two system tables, so if someone can help.. i will be thankfull.

And i wasn't confused about the db owner...

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

select o.name as TbName , xc.name as ColName

from sysobjects o

inner join syscolumns xc

on o.id = xc.id

where o.xtype = 'U'

hansel.ortiz Mr or Mrs. 500 Points: 585 More actions · Answer 4

MANU-J. (4/27/2010)
select o.name as TbName , xc.name as ColName
from sysobjects o
inner join syscolumns xc
on o.id = xc.id
where o.xtype = 'U'

well thanks.. it looks so simple.. maybe i was a little estressed doing something else..

but thanks for the help