object_name(id) returning Null

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » General » object_name(id) returning Null

object_name(id) returning Null

Rate Topic

Display Mode

Topic Options

Author

Message

Mike-292078

Posted Monday, April 21, 2008 8:08 AM

Forum 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

RyanRandall

Posted Monday, April 21, 2008 9:00 AM

SSCommitted

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

Mike-292078

Posted Monday, April 21, 2008 9:40 AM

Forum 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

Mike-292078

Posted Monday, April 21, 2008 9:52 AM

Forum 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

RyanRandall

Posted Monday, April 21, 2008 10:16 AM

SSCommitted

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 »

Permissions