Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

object_name(id) returning Null Expand / Collapse
Author
Message
Posted Monday, April 21, 2008 8:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 21, 2008 10:18 AM
Points: 3, Visits: 5
HI:

I'm doing some analysis trying to figure out what's making my database so big. I'm querying the sysindexes and I'm finding something very off.

There are rows in the table where id is null and there are rows where object_name(id) is returning null. There appears to be a lot of data in these.

Any idea what these object are?

Thanks
-Mike
Post #487988
Posted Monday, April 21, 2008 9:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652
Could it all be permissions related?

BOL (http://technet.microsoft.com/en-us/library/ms186301.aspx)...

"built-in functions such as OBJECT_NAME may return NULL if the user does not have any permission on the object"



Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.
Post #488032
Posted Monday, April 21, 2008 9:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 21, 2008 10:18 AM
Points: 3, Visits: 5
That doesn't sound right, I'm logged as the Administrator.
Post #488060
Posted Monday, April 21, 2008 9:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 21, 2008 10:18 AM
Points: 3, Visits: 5
OK, seems to be a side effect of using 'GROUP BY id with rollup' When I drop the rollup all the objects are found. odd???


SET NOCOUNT ON
select id, cast(object_name(id) as varchar(50)) AS name, sum(CASE WHEN indid<2 THEN rows END) AS rows,
sum(reserved)*8 AS reserved, sum(dpages)*8 AS data, sum(used-dpages)*8 AS index_size, sum(reserved-used)*8 AS unused
from dbo.sysindexes with (nolock) where indid in(0,1,255) and id>100
GROUP BY id with rollup
ORDER BY sum(reserved)*8 desc
Post #488071
Posted Monday, April 21, 2008 10:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652
Ah! The 'null' records are just the totals! It's all fine... :)


Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.
Post #488089
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse