Use the DBCC writepage command to modify the content on the physical page of SQL Server

----------------------------------------------------------------------------

---- This is andkylee's personal originality. Please repost it with respect to the author's Labor achievements;

---- The original source must be specified for reprinting.

:

Http://blog.csdn.net/andkylee

--- 2010-08-23 17:40:37

---- Keyword: DBCC writepage unmarshented command modify physical page

----------------------------------------------------------------------------

 

 

DBCC writepage is an undisclosed command in sqlserver 2000 and 2005. That is to say, any consequence of using these unofficially supported commands is irrelevant to Ms.

Similarly, this article only demonstrates the use of DBCC writepage. Any consequences arising during your operation are irrelevant to you.

 

The syntax of DBCC writepage is:

DBCC writepage ({dbid, 'dbname'}, fileid, pageid, offset, length, data)

 

 

The following describes how to use DBCC writepage:

----------------------------------------------------------------------------

 

Step 1: Create a test table

 

1>

2>

3> Create Table Test (ID int not null, name varchar (30) null)

4> go

1> insert into test

2> select 1, 'China'

3> go

(One row is affected)

1> insert into test

2> select 2, 'beijing'

3> go

(One row is affected)

1> select object_id ('test ')

2> go

-----------

1877581727

(One row is affected)

The table ID is 1877581727.

Step 2: view the page number of the data in the table

 

1> select * From sysindexes

2 & gt; where id = 1877581727

3> go

ID status first indid root minlen keycnt groupid dpages

Reserved used rowcnt rowmodctr reserved3 reserved4

Xmaxlen maxirow origfillfactor statversion reserved2 firstiam impid lockflags

Pgmodctr keys

Name

Statblob

Maxlen rows

------------------------------------------------------------------------

-------------------------------------------------------------------------

-------------------------------------------------------------------------

------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

-------------------------------------------------------------------------------

--------------------------------------------------------------------------------

-------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

-------------------------------------------------------------------------

1877581727 0 0x3b4f00
0 0x000000 8 0 1

1 2 2 2 0 0

57 0 0 0 0 0x3c4f00 0 0

0 null

Null

Null

8000 2

(One row is affected)

Query the page number of the first data page of the test table. In the sysindexes table, the first column indicates the page number of the first physical storage page of the object: 0x384f00, that is, the page number (1: 20283 ).

 

Step 3: query the data content before modification

 

The following queries the data content on the page () before the modification. We can see that there are two rows of records on the () page, which is consistent with the number of inserted two rows of records. Then, we can see that the headers of the page are China and Beijing,

This is "seemingly" consistent with the content of the inserted two rows of records.

Below I marked the content of two rows of test data in the test table in red. 1, 'China' 2, 'beijing'

 

 

1> DBCC page (9,1, 20283,2)

2> go

Data:

Memory Dump @ 0x518fc000

518fc000: 01010400 00800001 00000000 00000800 ?................

518fc010: 00000000 00000200 aa000000 721f8a00 ?............ R...

518fc020: 3b4f0000 01000000 92010000 ?; O...

518fc030: 02000000 00000000 00000000 00000000 ?................

518fc040: 01000000 00000000 00000000 00000000 ?................

518fc050: 00000000 00000000 00000000 00000000 ?................

518fc060: 30000800 01000000
0200fc01 00140063? 0 ......

518fc070: 68696e61 30000800 02000000
0200fc01? Hina
0 ...........

518fc080: 00160062 65696a69 6e670000 21212121 ?... Beijing
..!!!!

518fc090: 21212121 21212121 21212121 21212121 ?!!!!!!!!!!!!!!!!

518fc0a0: 21212121 21212121 21212121 21212121 ?!!!!!!!!!!!!!!!!

...... These are unallocated Space

518fdfc0: 21212121 21212121 21212121 21212121 ?!!!!!!!!!!!!!!!!

518fdfd0: 21212121 21212121 21212121 21212121 ?!!!!!!!!!!!!!!!!

518fdfe0: 21212121 21212121 21212121 21212121 ?!!!!!!!!!!!!!!!!

518fdff0: 21212121 21212121 21212121 74006000 ?!!!!!!!!!!!! T .'.

Offset Table:

Row-offset

1 (0x1)-116 (0x74)

0 (0x0)-96 (0x60)

DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

 

Step 4: modify the content of the physical data page

 

According to the output result of the preceding DBCC page, the offset of the content in the "China" column is 111, and the five characters "China" are changed to "aaaa. This is the simplest. The asicc of A is 97 (0x61 ).

 

1>DB
CC
Writepage (20283,111, 6161616161, X)

2>Go

DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

 

Step 5: view the modified physical page content

1> DBCC page (9,1, 20283,2)

2> go

Data:

Memory Dump @ 0x5002c000

5002c000: 01010400 00820001 00000000 00000800 ?................

5002c010: 00000000 00000200 aa000000 721f8a00 ?............ R...

5002c020: 3b4f0000 01000000 92010000 75000000 ?; O...

5002c030: 02000000 00000000 00000000 8234666e ?............. 4fn

5002c040: 00000000 00000000 00000000 00000000 ?................

5002c050: 00000000 00000000 00000000 00000000 ?................

5002c060: 30000800 01000000
0200fc01 00140061? 0 ......

5002c070: 61616161 30000800 02000000
0200fc01? Aaaa
0 ...........

5002c080: 00160062 65696a69 6e670000 21212121 ?... Beijing
..!!!!

5002c090: 21212121 21212121 21212121 21212121 ?!!!!!!!!!!!!!!!!

5002c0a0: 21212121 21212121 21212121 21212121 ?!!!!!!!!!!!!!!!!

5002c140: 21212121 21212121 21212121 21212121 ?!!!!!!!!!!!!!!!!

5002c150: 21212121 21212121 21212121 21212121 ?!!!!!!!!!!!!!!!!

............................ Omitted not occupying space

5002dfe0: 21212121 21212121 21212121 21212121 ?!!!!!!!!!!!!!!!!

5002dff0: 21212121 21212121 21212121 74006000 ?!!!!!!!!!!!! T .'.

Offset Table:

Row-offset

1 (0x1)-116 (0x74)

0 (0x0)-96 (0x60)

DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

 

Step 6: Use the client tool to verify the modified data content

1> select * from test

2> go

ID name

-----------------------------------------

1 aaaaa

2 Beijing

(2 rows affected)

We can see that the name column of the first record is changed from China to aaaaa

 

Note:
In the preceding example, only the data in a column of a row is modified, and the modified content is consistent with the original content.

 

If you modify multiple columns or the length of the fields before and after the modification is inconsistent, further modifying the data of the entire row or modifying the data of the entire page may be much more complicated.