Sometimes, while searching the root cause of database problems, we face page IDs and need to find to which object that page belongs.<\/p>\n
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 ?<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
We will create the user defined type with the following code:<\/p>\n
create type PageIDs as TABLE\n(database_id int,\nfile_id int,\npage_id varchar(30) )\ngo<\/pre>\nNow we can create the stored procedure:<\/p>\n
Create procedure GetObjectNames \n@Tab PageIDs READONLY -- we use the user defined type\nas\nset nocount on\n\n-- Create a temp table with one more field\n-- objectName\nselect database_id,file_id,page_id,\n cast('' as varchar(100)) as objectName\n into #tmpResult\nfrom @Tab\n\n declare @database_id int\n declare @file_id int\n declare @page_id varchar(20)\n declare @sql varchar(100)\n declare @objName varchar(100)\n\n-- Temp table variable for the insert\/exec\ndeclare @tabtmp table\n(parentObject varchar(100),\n [Object] varchar(150),\n Field varchar(100),\n value varchar(100) )\n\n-- cursor over the temp table\n-- we need to execute the dbcc page\n-- for each row in our temp table\ndeclare ct cursor for \n select database_id,file_id,page_id\n from #tmpResult\n\nopen ct\nfetch next from ct into @database_id,@file_id,@page_id\nwhile @@FETCH_STATUS=0\nbegin\n -- DBCC Page is built as string\n select @sql='DBCC PAGE(' + cast(@database_id as varchar(100)) + \n ',' + cast(@file_id as varchar(100)) + ',' + @page_id + ') with tableresults'\n\n -- insert into temp table variable\n -- the executio of the string\n insert into @tabtmp\n exec(@sql)\n\n -- Retrieve from the temp table variable\n -- the object name of this page\n select @objName=object_name(value,@database_id) from @tabtmp\n where field='Metadata: ObjectId'\n\n -- Update the temp table with the object name\n update #tmpResult set objectName=@objName\n where current of ct\n\n -- Clear the temp variable and\n -- get the next record\n delete @tabtmp \n fetch next from ct into @database_id,@file_id,@page_id\nend\n close ct\n deallocate ct\n -- Return the result\n select * from #tmpResult<\/pre>\nThe 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 :<\/p>\n
declare @t PageIDs\n\ninsert into @t values \n (20,1,'64424'),\n (20,1,'10224'),\n (20,1,'50851')\n\n exec GetObjectNames @t\n\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-8099","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8099","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=8099"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8099\/revisions"}],"predecessor-version":[{"id":42499,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8099\/revisions\/42499"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=8099"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=8099"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=8099"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=8099"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}