sql server programming: Hello All,

Would anyone know how to convert the hexadecimal value of the first & root
column of the sysindexes table.

I need the decimal equivalent as I would want to use DBCC PAGE to trace
the Index Pages.

Looks like I'm making a mistake as I get the following error when I
convert 0x9E0300000100


Thanks,
rgn

DBCC TRACEON(3604)
GO
DBCC PAGE(5,1,40451,1)
GO

Server: Msg 8968, Level 16, State 1, Line 1
Table error: DBCC PAGE page (1:40451) (object ID 0, index ID 0) is out of
the range of this database.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

Hi rgn

I give out this script in our SQL Server Internals Class. It will translate
the First, Root and FirstIAM columns in sysindexes.

- Convert page numbers in sysindexes to decimal form for use
-- with DBCC commands


/* You can modify this code to accept a table name and/or index
name, and then only return the corresponding rows
from sysindexes.
This script is provided 'as-is'
*/

select convert(char(30), name) 'name',
id,
indid,
convert(varchar(2), (convert(int, substring(first, 6, 1)) * power(2, 8)) +
(convert(int, substring(first, 5, 1)))) + ':' +
convert(varchar(11),
(convert(int, substring(first, 4, 1)) * power(2, 24)) +
(convert(int, substring(first, 3, 1)) * power(2, 16)) +
(convert(int, substring(first, 2, 1)) * power(2, 8)) +
(convert(int, substring(first, 1, 1)))) 'first',
first,
convert(varchar(2), (convert(int, substring(root, 6, 1)) * power(2, 8)) +
(convert(int, substring(root, 5, 1)))) + ':' +
convert(varchar(11),
(convert(int, substring(root, 4, 1)) * power(2, 24)) +
(convert(int, substring(root, 3, 1)) * power(2, 16)) +
(convert(int, substring(root, 2, 1)) * power(2, 8)) +
(convert(int, substring(root, 1, 1)))) 'root',
root,
convert(varchar(2), (convert(int, substring(firstIAM, 6, 1)) * power(2, 8))
+ (convert(int, substring(firstIAM, 5, 1)))) + ':' +
convert(varchar(11),
(convert(int, substring(firstIAM, 4, 1)) * power(2, 24)) +
(convert(int, substring(firstIAM, 3, 1)) * power(2, 16)) +
(convert(int, substring(firstIAM, 2, 1)) * power(2, 8)) +
(convert(int, substring(firstIAM, 1, 1)))) 'firstIAM',
firstIAM
from sysindexes


--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


[quoted text, click to view]

The six bytes holding the page address (2 bytes for the file and 4 bytes for
the page) are stored in a swapped (mirrored) format. Meaning that the first
byte is stored last, the second is stored second to last and so on. In order
to get the correct file and page numbers, you need to extract and
concatenate the correct bytes:
6+5 for the file#, 4+3+2+1 for the page#, like so:

DECLARE @binpageaddr AS BINARY(6)
SET @binpageaddr = 0x9E0300000100

SELECT
CAST(
SUBSTRING(@binpageaddr, 6, 1)
+ SUBSTRING(@binpageaddr, 5, 1)
AS INT) AS file#,
CAST(
SUBSTRING(@binpageaddr, 4, 1)
+ SUBSTRING(@binpageaddr, 3, 1)
+ SUBSTRING(@binpageaddr, 2, 1)
+ SUBSTRING(@binpageaddr, 1, 1)
AS INT) AS page#

--
BG, SQL Server MVP
www.SolidQualityLearning.com


[quoted text, click to view]