Sybase数据库技术，数据库恢复专家

Most of us know how to update system table in sql server 2000, but there is rare information about how to do it in sql 2005. We know, Microsoft doesn't like anyone that is not in Microsoft company to update sql server system tables. But he always likes to leave a back door for himself. I think, the men, who commited themselves to design and develop SQL sever 2005 in Microsoft, would be the first ones know how to update SQL 2005 system table. And I look for related infomation in msdn again and again, finally, I conclued the following way to update sql 2005 system table.

Two necessary conditions for updating sql 2005 system table.

1. Start sql server 2005 instance in single-user mode.
2. Connect to sql server 2005 instance via DAC(dedicated administrator connections)

I will demo how to update sql 2005 system table as follows:

1. Enter windows services management : [start] -- [run] --[services.msc]
2. Focus on your sql server instance name. [right button] -- [property]
3. Find sqlservr.exe path in [Path to executable]

For me the sqlservr.exe install path is "D:\Microsoft SQL Server\MSSQL.1\MSSQL\Binn" Let's see how to start sql 2005 instance in single-user mode.

C:\> d:
D:\> cd D:\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
D:\Microsoft SQL Server\MSSQL.1\MSSQL\Binn> sqlservr.exe -sSQL2005 -m

'-sSQL2005' said that the sql server instance name is 'SQL2005'. '-m' parameter said that we will start sql server in single-user mode. If successful, then SQL Server instance start up. If it can not start, you can restart the computer and try again. if error again, you can seek helps on net.

2. Connect sql server 2005 via DAC

The normal way to connect sql 2005 in DAC mode is sqlcmd -A

c:\> sqlcmd -E -S MYPC\SQL2005 -A

If you'd like to use login name and password, references as follows:

c:\> sqlcmd -U sa -P *** MYPC\SQL2005 -A

If DAC IP and port of your sql 2005 are : 127.0.0.1 and 1183. References as follows:

c:\> sqlcmd -U sa -P *** 127.0.0.1,1183

Note: here we don't need a -A param in 'sqlcmd' command. DAC connection port is different from the common sql server connection. but how to get the SQL Server DAC port个 It is easy: when start sql server in windows command line. there is a lot of log info to echo on the screen. locate the info contain string "Dedicated admin connection". for me it is:

Server is listening on [ 127.0.0.1 [ipv4] 1183].
Dedicated admin connection support was established for listening locally on port 1183.

The above information tell us that sql 2005 listing on 127.0.0.1:1183 to accept DAC client request.The results in command line are disorderly and unsystematic. Luckily, we can also use DAC connection through SQL Server Management Studio(SSMS).

Note that the "Server name" is "127.0.0.1,1183". in addition there will be an error prompt:

Don't worry, just ignore it.

3. Update sql 2005 system table example

use master
go

create table ddd(id int not null)
go

insert into ddd(id) values(10)
go

We create a new table called "ddd". now, I show the meta data of "ddd" in sql 2005 system table.

select * from sys.sysschobjs where name = 'ddd'

I ignore two fields "creatd", "modified", for a nice format in web page.

id         name  nsid nsclass status   type pid  pclass intprop  created modified
---------- ----- ---- ------- -------- ---- ---- ------ -------- ------- --------
1211151360  ddd   1    0       917504   U    0    1      1       2008*** 2008***

I rename table from "ddd" to "sqlstudy" in the following:

update sys.sysschobjs set name = 'sqlstudy' where name = 'ddd'

Warning: System table ID 34 has been updated directly in database ID 1 and cache coherence
may not have been maintained. SQL Server should be restarted.

Because "cache coherence" problem, may make us restart the sql server instance to see the changes.

select * from sqlstudy

id
-------
10

View the system tables in sql server 2005 master db.

select name from sys.all_objects where type = 'S' order by name

转自：http://hi.baidu.com/luck001221/blog/item/9c3fdc224dcb1f49ac34de6a.html