PASS Data Community Summit logo

PASS Data Community Summit 2023

Get all the latest announcements direct to your inbox

Identify Object Name from Page ID

Sometimes, while searching the root cause of database problems, we face page IDs and need to find to which object that page belongs.

We can find this information using DBCC Page, but it’s a manual procedure, we need to execute this command and look the text result to find the object id. This would be enough if we were looking for only one page id, but how to solve this problem when we are looking for several page ids ?

We can use ‘with tableresults’ clause with the DBCC Page statement to get the results in a table format, but we need to filter the result, so we need to use insert/exec to insert the result in a temporary table and then filter the result.

The insert/exec instruction can’t be used inside a function, so we will need to create one stored procedure that will receive a table parameter with the page ids that we need to transform in object names.

To use table parameter in a stored procedure we need first to create a table user defined table and them use this new user defined type to create the stored procedure.

We will create the user defined type with the following code:

Now we can create the stored procedure:

The way to use the stored procedure can be quite different depending on the source of the page ids, but one simple example will be like this :