November 11, 2010 at 2:51 pm
I made a utility that will list all of the large tables in my databases by first doing a query as follows:
select a.name, a.id, b.name
from dbo.sysobjects a inner join dbo.sysusers b on (a.uid = b.uid)
where xtype in('S','U')
order by a.name
and then will loop through the listing and do a
exec sp_spaceused <table_owner>.<table_name>
It works great with the exception that in one of my databases I have a to a owner.table combination that is invalid (i.e. there is not table with the owner and name combination).
How do I clean the dbo.sysobjects and dbo.sysusers tables? I am thinking that I just need to remove the reference from the dbo.sysobjects.
Any help is appreciated.
November 11, 2010 at 3:05 pm
the problem is not about cleaning system views; it's that the old views can now give you inaccurate information; using the newer correct views will solve the issue.
the trouble is you are using the old system views appropriate for SQL 2000 which were "user" based; in 2005 and above you really need to use the new schema_id instead, then you will not have invalid references to users that don't exist: schemas always exist, where a user may not.
in 2005 and up, the sysobjects.uid is no longer a FK to sysusers...it is actually the schema_id underneath instead; so schema_id = 2 might be 'dev', but sysusers.uid = 2 might be 'bob', so when you put it together, you are getting bob.tablename, when it is really dev.tablename, for example. joining to the new tables , oir better, using the new views is what you'll need to start training yourself to do.
select name, object_id,schema_name(SCHEMA_ID) from sys.objects
where type in('S','U')
and is_ms_shipped=0
--or
select a.name, a.object_id,b.name
from sys.objects a
inner join sys.schemas b on a.schema_id=b.schema_id
where a.type in('S','U')
and a.is_ms_shipped=0
Lowell
November 12, 2010 at 8:15 am
Outstanding! And thanks. I think that this should get me through.
This board always gets me through when I have issues.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply