cancel
Showing results for 
Search instead for 
Did you mean: 

Strange result with ROW_count and systabstats

Former Member
0 Kudos

use master  --->  DB name is different than from clause .

go

select  Table_Name=convert(char(30),name),ROW=row_count(db_id(),id)

from ops..sysobjects where type='U' and name='XXXX'

go

>>>>> NO RESULT

use ops

go

select  Table_Name=convert(char(30),name),ROW=row_count(db_id(),id)

from ops..sysobjects where type='U' and name='XXXX'

go

>>>>> Got result

Note : Same issue with systabstats (Now result if use <DB> and <>.systabstats   -- referring different database .

is this feature or bug ?

use master

go

select rowcnt,* from pos..systabstats where object_name(id)='P_PosBatchRpt'

go

>>>>>>>>>>> NO result

use pos

go

select rowcnt,* from pos..systabstats where object_name(id)='P_PosBatchRpt'

go

>>>>>>>>>>>> Got result .

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Ohh ya Got it ... over sighted db_id()  ..

Thanks .

Ajay Pandey

Former Member
0 Kudos

but i thought rowcnt is physical column name in systabstats ..

use master

go

select rowcnt,* from pos..systabstats where object_name(id)='P_PosBatchRpt'

go

>>>>>>>>>>> NO result

use pos

go

select rowcnt,* from pos..systabstats where object_name(id)='P_PosBatchRpt'

go

>>>>>>>>>>>> Got result .

dan_thrall
Participant
0 Kudos

It is a column in systabstats however remember object_name() will look in the current database if you do not use the dbid parameter.

Your example:

use master

go

select rowcnt,* from pos..systabstats where object_name(id)='P_PosBatchRpt'

go

When object_name(id) is run from master it will run object_name() in the master using id's from pos.  In other words, it will be using master..sysobjects not pos..sysobjects.

Alternative example showing the difference.  Using sysdatabases is a good test here since sysdatabases only exists in the master database.  This ensures the example will catch cross database lookup.

1> select db_name(), object_name(id) from master..sysobjects where object_name(id)="sysdatabases"

2> go

                                                                                                                                                                                                                                                                                                                                                                                                                                                     

------------------------------------------------------------

         ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

(0 rows affected)

1> select db_name(), object_name(id,db_id("master")) from master..sysobjects where object_name(id,db_id("master")) = "sysobjects"

2> go

                                                                                                                                                                                                                                                                                                                                                                                                                                                     

------------------------------------------------------------

         ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

repro

         sysobjects                                                                                                                                                                                                                                                                                                                                                                                                                                  

(1 row affected)

Dan

Answers (2)

Answers (2)

Former Member
0 Kudos

Got it ...Thanks Mark .

Thanks

Ajay Pandey

dan_thrall
Participant
0 Kudos

Hi Pandey,

This is due to the scope of the builtin functions object_name(), row_count() and db_id().

When db_id() is called with no arguments it will look in the current database.

When object_name() is called without the database id parameter, it looks only in the current database.

Example of a rewrite of the first example:

select  Table_Name=convert(char(30),name),ROW=row_count(db_id(),id)

from ops..sysobjects where type='U' and name='XXXX'

To (from repro database)

1> select Table_Name=convert(char(30),name),ROW=row_count(db_id("master"),id)

2> from master..sysobjects where type = "S" and name = "sysdatabases"

3> go

Table_Name

         ROW

------------------------------------------------------------

         ---------------------

sysdatabases

                            15

(1 row affected)

Second example

select rowcnt,* from pos..systabstats where object_name(id)='P_PosBatchRpt'


Change to: (from repro database)

select rowcnt, * from master..systabstats where object_name(id,db_id("master"))="sysdatabases"

[output snipped]


Naturally master's dbid is 1, but this illustrates the flexibility of embedding db_id() into object_name()


The object_name can accept a database argument.

object_name(<object_id>[, <database_id>])

object_id can accept a fully qualified name

object_id(<object_name>)



HTH

Dan