Post reply

How to get object name from server wide DMV ?

  • February 3, 2011 at 10:24 am

    #234395

    I am doing some performance tuning and currently working with sys.dm_exec_procedure_stats. This is server wide DMV, it displays database_id, object_id. If I want to join to sys.procedures, I am limited only to procedures in current database and I am forced to use Where database_id=db_id().

    However, I would like to check procedures accross all databases. Is there a way (besides cursor and dynamic SQL) to do it?

  • February 3, 2011 at 11:30 am

    #1282063

    Look at this item as determine if it will the work you require.

    It is for SQL 2000 and may have to be modified slightly:

    A more efficient ForEach routine

    http://www.sqlservercentral.com/scripts/Miscellaneous/30900/

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • February 3, 2011 at 12:43 pm

    #1282103

    No need to use a DMV at all. In SQL 2005 SP2, the object_name function was enhanced and a second, optional, parameter added. The second parameter is database_id

    So, in the select you can just say ... OBJECT_NAME(object_id, database_id) AS ProcedureName, ....

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 3 posts - 1 through 3 (of 3 total)

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