How To List All Pages of Table in SQL Server

To find all pages of table in SQL server we can use 2 methods

  1. DBCC IND: Oldest dbcc command.
  2. DMV:”sys.dm_db_database_page_allocations” this is DMV available from SQL 2102 onwards.


--Syntax
1. DBCC IND(Database Name,Tale Name,-1)
--OR For SQL 2012 onward
2. select * from
sys.dm_db_database_page_allocations(@dbid,@objectid,null,null,null)



-- Example:
Create table master.dbo.test (test int)
GO
insert into master.dbo.test values (1)
Go

DBCC IND('master',test,-1)

GO --OR For SQL 2012 onward
select * from sys.dm_db_database_page_allocations(db_id('master'),object_id ('test'),null,null,null)
GO

Drop table Test – Cleanup

Output:


1 comment: