SQL 2005 system tables

Question

Post reply

SQL 2005 system tables

Uma-207256

SSC Enthusiast

Points: 147
More actions
February 23, 2006 at 9:26 am

#111700

we have a query to update the statistics on system tables in SQL2005 ..
The old query is like this ..( SQL2000)
select so.name,
  su.name,
  so.type
from test_db..sysobjects so,
  test_db..sysusers su
  where type='S' and
  su.uid = so.uid
then executing query like this..
update statistics on test_db.systable .....
when I tried to execute some thing like this on SQL2005 , it is not identifying some system tables ( even though they are returning from query . Eg : sysasymkeys,sysxprops etc ...)
any ideas ..

Thanks You

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

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

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello Uma,

Please go through the Books Online on the flg topic

Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views

Thanks and have a nice day!!!

Lucky

Uma-207256 SSC Enthusiast Points: 147 More actions · Answer 2

Thankyou for your response .

My question is if you want to update statistics of system tables in SQL2005 , what kind of query you will go with ?

If you write select name from sys.all_objects where type='S' :

after that it will give the same problem .it is giving like sys.<tablename> is not identified ..

If you have any thoughts about this pls let me know.

Thanks

DCPeterson SSCoach Points: 15266 More actions · Answer 3

You can't manually update statistics on the system tables. Frankly I can't think of a reason you'd need to.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

Uma-207256 SSC Enthusiast Points: 147 More actions · Answer 4

dcpeterson ..Thanks for your response .

But we can update statistics on system tables in SQL2000 .

update statistics sysservers;

we can't we do it in SQL2005 ?

DCPeterson SSCoach Points: 15266 More actions · Answer 5

No, MS finally gave us a usable set of system views in 2005 and removed the ability to directly query or change the system tables. As I understand it, even the term "system tables" is somewhat of a misnomer in 2005 since they are not really persisted to disk as such.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/