1. 查找表包含的页信息:
查找表包含的页信息可以使用 dbcc ind
语法:
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
)
(
[ '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
)
例如:
dbcc
ind(
9
,tablename,
-
1
)
2. 查找页所在的表(一个页只能属于一个对象)
查找页所在的表,可以用 dbcc page
语法:
DBCC
PAGE
(
[ 'database name'|database id ] , -- can be the actual name or id of the database
file number , -- the file number where the page is found
page number , -- the page number within the file
print option = [ 0|1|2|3 ] -- display option; each option provides differing levels of information
)
(
[ 'database name'|database id ] , -- can be the actual name or id of the database
file number , -- the file number where the page is found
page number , -- the page number within the file
print option = [ 0|1|2|3 ] -- display option; each option provides differing levels of information
)
例如:(注意需要开启3604标记)
DBCC
TRACEON (
3604
)
DBCC PAGE( ' dbname ' , 1 , 5253 , 1 ) -- with tableresults
DBCC TRACEOFF ( 3604 )
DBCC PAGE( ' dbname ' , 1 , 5253 , 1 ) -- with tableresults
DBCC TRACEOFF ( 3604 )
输出如下:
可以得到 Metadata: ObjectId = 478676803 和 Metadata: IndexId = 1
根据这两个信息可以得到具体信息
select
s.name,o.name,i.id,i.indid,i.name
from
sys.sysobjects o inner join sys.schemas s on o.uid = s.schema_id inner join sys.sysindexes i on o.id = i.id
where o.id = 478676803 and i.indid = 1
sys.sysobjects o inner join sys.schemas s on o.uid = s.schema_id inner join sys.sysindexes i on o.id = i.id
where o.id = 478676803 and i.indid = 1
exec
sp_helpindex
'
dbname.dbo.tablename’