--
----------------------------------------------------------------------
-- 查看表存储元数据(分区信息,分配单元信息,页面信息,首页地址)
declare @tablename nvarchar( 100)
set @tablename = 数据库名称
select convert( char( 8), object_name(i. object_id)) as table_name,
i.name as index_name, i.index_id, i.type_desc as index_type,
partition_id as ' 分区ID ', partition_number as ' 分区数量 ', rows as ' 记录行数 ',
a.allocation_unit_id as ' 分配单元ID(allocation_unit_ID) ',
a.type_desc as page_type_desc, a.total_pages as pages,
iau. filegroup_id as ' 文件组ID ',iau. *
from sys.indexes i join sys.partitions p
on i. object_id = p. object_id and i.index_id = p.index_id
join sys.allocation_units a
on p.partition_id = a.container_id
join sys.system_internals_allocation_units iau
on p.partition_id = iau.container_id
where i. object_id = object_id( @tablename)
-- 查看表包含的页面信息(实际使用的页面)
-- ----------------------------------------------------------
/*
DBCC IND(
['database name'|database id], -- the database to use
table name, -- the table name to list results
index id, -- an index_id from sys.indexes; -1 shows all indexes and IAMs, -2 just show IAMs
)
*/
declare @dbName nvarchar( 100), @tablename nvarchar( 100)
set @dbName = 数据库
set @tablename = 表名
DBCC IND( @dbName, @tablename, 1)
-- ----------------------------------------------------------------------
-- 查看单个数据页详情
dbcc traceon( 3604)
go
dbcc page(数据库名称, 1,数据页编号, 1)
-- 查看表存储元数据(分区信息,分配单元信息,页面信息,首页地址)
declare @tablename nvarchar( 100)
set @tablename = 数据库名称
select convert( char( 8), object_name(i. object_id)) as table_name,
i.name as index_name, i.index_id, i.type_desc as index_type,
partition_id as ' 分区ID ', partition_number as ' 分区数量 ', rows as ' 记录行数 ',
a.allocation_unit_id as ' 分配单元ID(allocation_unit_ID) ',
a.type_desc as page_type_desc, a.total_pages as pages,
iau. filegroup_id as ' 文件组ID ',iau. *
from sys.indexes i join sys.partitions p
on i. object_id = p. object_id and i.index_id = p.index_id
join sys.allocation_units a
on p.partition_id = a.container_id
join sys.system_internals_allocation_units iau
on p.partition_id = iau.container_id
where i. object_id = object_id( @tablename)
-- 查看表包含的页面信息(实际使用的页面)
-- ----------------------------------------------------------
/*
DBCC IND(
['database name'|database id], -- the database to use
table name, -- the table name to list results
index id, -- an index_id from sys.indexes; -1 shows all indexes and IAMs, -2 just show IAMs
)
*/
declare @dbName nvarchar( 100), @tablename nvarchar( 100)
set @dbName = 数据库
set @tablename = 表名
DBCC IND( @dbName, @tablename, 1)
-- ----------------------------------------------------------------------
-- 查看单个数据页详情
dbcc traceon( 3604)
go
dbcc page(数据库名称, 1,数据页编号, 1)