Post reply

object_name(id) returning Null

  • April 21, 2008 at 8:08 am

    #121817

    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

  • April 21, 2008 at 9:00 am

    #805034

    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.

  • April 21, 2008 at 9:40 am

    #805055

    That doesn't sound right, I'm logged as the Administrator.

  • April 21, 2008 at 9:52 am

    #805065

    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

  • April 21, 2008 at 10:16 am

    #805082

    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.

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

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