object_name (st.objectid) returns NULL. How can I overcome/fix this?

Question

Post reply

object_name (st.objectid) returns NULL. How can I overcome/fix this?

VoldemarG

Hall of Fame

Points: 3883

February 26, 2021 at 5:13 pm

#3850672

How can I overcome/fix this?

object_name (st.objectid) in the code below returns NULL. (in the below query, as well as any other similar queries..).

White paper says "A user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as OBJECT_NAME may return NULL if the user does not have any permission on the object. "

However, on this server I am a member of sysadmin. in SQL Server, as well as full Windows (2012 ENT) server admin which allows me to do everything on that server, every imaginable and unimaginable operation that I want. Except of seeing the darn NAME of the object in queries that use object_name (st.objectid) function.

select top 10 rank() over(order by total_worker_time desc,sql_handle,statement_start_offset) as row_no
,       (rank() over(order by total_worker_time desc,sql_handle,statement_start_offset))%2 as l1
,       creation_time
,       last_execution_time
,       (total_worker_time+0.0)/1000 as total_worker_time
,       (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
,       total_logical_reads as [LogicalReads]
,       total_logical_writes as [logicalWrites]
,       execution_count
,       total_logical_reads+total_logical_writes as [AggIO]
,       (total_logical_reads+total_logical_writes)/(execution_count + 0.0) as [AvgIO]
,   case when sql_handle IS NULL
                then ' '
                else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1        then len(convert(nvarchar(MAX),st.text))*2      else qs.statement_end_offset    end - qs.statement_start_offset) /2  ) )
        end as query_text 
,       db_name(st.dbid) as database_name
,       st.objectid as object_id, object_name (st.objectid) [OBJECT_NAME]
from sys.dm_exec_query_stats  qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where total_worker_time > 0 
and last_execution_time between '2021-02-26 09:00' and '2021-02-26 11:30'
order by total_worker_time  desc

Likes to play Chess

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

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

Since it appears that you may be working from a different database via the code, here's a possible hint from the documentation on OBJECT_NAME (emphasis is mine)...

OBJECT_NAME ( object_id [, database_id ] )

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

VoldemarG Hall of Fame Points: 3883 More actions · Answer 2

Ok. I see. I need to create loop or cursor or msforeachdb... over 500 databases. yes doable but cumbersome.

Likes to play Chess

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

Not at all. sys.dm_exec_sql_text() returns the database ID... use it in conjunction with what I posted about OBJECT_NAME() above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

VoldemarG Hall of Fame Points: 3883 More actions · Answer 4

ok. fixed by

, object_name (st.objectid, st.dbid)

THANK YOU!

Likes to play Chess

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

You bet. Thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)