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:

Read:  Frequently Used Queries For Audit In Sql Server