How can I see the pages used by my table?

Not always, but there are instances that you need to see the data pages used by SQL Server for holding data in our tables. There are few ways of seeing them, the easiest way is, using two undocumented functions called sys.fn_PhysLocFormatter and sys.fn_PhysLocCracker. These two were initially released with SQL Server 2008 and they are still available. Not sure why they are still marked as undocumented but information throw from them is really useful.

Here is a test code on it;

USE AdventureWorksDW2014;
GO

-- Checking with fn_PhysLocFormatter function.
-- This shows file id, page id and slot id together.
SELECT sys.fn_PhysLocFormatter (%%physloc%%) AS RID, * FROM dbo.FactInternetSales;


-- If you need file, page and slot separately, 
-- fn_PhysLocCracker function can be used.
SELECT sys.fn_PhysLocFormatter (%%physloc%%) AS RID, RID_c.file_id, RID_c.page_id, RID_c.slot_id, * 
FROM dbo.FactInternetSales
 CROSS APPLY sys.fn_PhysLocCracker (%%physloc%%) AS RID_c
ORDER BY RID_c.file_id, RID_c.page_id, RID_c.slot_id;