Tables missing from sysobjects
-
Hello,
I was wondering if anyone else has run into a situation where you have tables in a database but some of them do not show when you look in the Master database sysobjects table.
If so then how would you fix it?
Do you really need to fix it?
How would you stop it from happening again?
Thanks
William O'Malley
-
I believe that sysobjects is per database for that db's objects only. If there are objects in your DB that are not in your db's sysobject table then I don't know how to resolve...
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Yes sysobjects is in each db, so the table should be listed in its own database.
I don't think it's possible to have orphaned objects in a db... but we never know.
-
The only other possibility is a temp table which will be listed in its' own database and 'tempdb'...
**ASCII stupid question, get a stupid ANSI !!!** -
I can't test this fromhere but I think that temp table are ONLY listed in tempdb.
-
How do you mean Remi?!?! An object_id will be created only in tempdb but surely the table exists in the database it was created in (temp or not..) ?!?!
Did I misunderstand something or were you just saying the same thing that I was ?!?!(can't figure out if your response is to my post or to William's)...
**ASCII stupid question, get a stupid ANSI !!!** -
sysobjects is a table that exists in every database.
Here's a link for a reference on system tables innSQL 2000:
http://www.microsoft.com/sql/techinfo/productdoc/2000/systables.mspx
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
yes - I think William was asking that if a table exists in the db sysobjects then is it possible to not see it in the master sysobjects....
**ASCII stupid question, get a stupid ANSI !!!** -
master sysobjects contains the objects of ... well master
local db sysobjects contains the objects of ... well local db sysobjects
One sysobject per database
* Noel -
All is clear now...
"master sysobjects contains the objects of ... well master
local db sysobjects contains the objects of ... well local db sysobjects
One sysobject per database"
....but temp tables are listed in tempdb sysobjects and NOT the local db!
**ASCII stupid question, get a stupid ANSI !!!** -
That's more like it
. -
ok.. i think i get it now.
So if I have two databases
Master
Production
each will have it's own unique sysobjects entries.
thanks for the reply's
William O'Malley
-
Yes, each database will always have it's own table sysobjects with entries for all the objects in that database. However I am not sure about your understanding and use of the database named Master. Every SQL Server installation has a system database called master. This database contains (most of) the metadata that SQL Server needs to run, for instance information about which databases are available and the accounts that are allowed to login to the server.
As has been said above, if you look in the sysobjects table in master you find entries for every table that exists in master (e.g. sysdatabases, syslogins and of course sysobjects itself). If you switch to your own database and look in sysobjects you will find entries for all tables that exist there. These will be either system tables (such as sysobjects, syscolumns etc) that contain metadata about that database, and of course the user tables that you (or someone else) have created in that database.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply