Finding the names of objects in the Resource database

All we need is an easy explanation of the problem, so here it is.

I’m looking at the sys.dm_exec_function_stats view to find scalar function stats. That view also includes rows from a database with an id 32767, which I’ve found is the Resource database.

Some of these IDs can be passed to OBJECT_NAME() or OBJECT_DEFINITION() Metadata functions but some IDs return NULL

SELECT SERVERPROPERTY('ResourceVersion')  AS 'ResourceVersion'
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime') AS 'ResourceLastUpdateDateTime' 

SELECT 
    defs.database_id
    , defs.object_id
    , OBJECT_NAME(defs.object_id) AS objName
    , OBJECT_DEFINITION(defs.object_id) AS objDef
    , OBJECT_NAME(defs.object_id, defs.database_id) AS objNameDB
    , OBJECT_DEFINITION(defs.object_id, defs.database_id) AS objDefDB
FROM sys.dm_exec_function_stats AS defs
WHERE defs.database_id = 32767

Finding the names of objects in the Resource database

I can find the fn_hadr_is_primary_replica function (object_id = -665919249) in the sys.all_objects or in sys.all_sql_modules, but not the object_id = -706609674.

Is there any way how to find the object name or definition of the -706609674?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

In my instance, object id -706609674 is sys.fn_cdc_jobs.

The resource database is just another database with files you can make a copy of and attach under a different name.

The files are named mssqlsystemresource.mdf and mssqlsystemresource.ldf.

You can find them in the subdirectory \MSSQL\Binn for your installation.

Alternatively, run your commands from the DAC.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply