Posted by ssingh on 07/19/06 13:55

Hello,
I want to select the names and number of rows of all tables in the
current database, whose name starts with 'sys'
my query is:

select o.[name], sum(i.[rows]) as numbers
from sysobjects o
inner join sysindexes i
on (o.[id] = i.[id])
where o.[name] like 'sys%'and i.indid=1
group by o.[name]


the result of the query is (depending of the database) :


name numbers
sysaltfiles 14
syscharsets 114
syscolumns 4934
syscomments 2035
sysconfigures 38
sysdatabases 7
sysdepends 5524
sysdevices 6
sysfilegroups 1
sysfulltextcatalogs 0
sysfulltextnotify 0
sysindexes 98
syslanguages 33
sysmessages 3795
sysobjects 1285
syspermissions 806
sysproperties 0
sysreferences 0
sysservers 1
systypes 26
sysusers 14
sysxlogins 3


But when I count the number of rows of "sysobjects", I get
numbers=1298, which is different from the result displayed above and
same goes for "syscolumns".
I also tried "dbcc updateusage [0]" to update the SQL Server but I
didnt help me.
Can anyone please advice me on this behaviour?

[Back to original message]