SQL2005 如何更新系统表

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]

sql 2005 update system table -- services

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 : and 1183. References as follows:

c:\> sqlcmd -U sa -P ***,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 [ [ipv4] 1183].
Dedicated admin connection support was established for listening locally on port 1183.

The above information tell us that sql 2005 listing on 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 ",1183". in addition there will be an error prompt:

Don't worry, just ignore it.

3. Update sql 2005 system table example

use master

create table ddd(id int not null)

insert into ddd(id) values(10)

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

View the system tables in sql server 2005 master db.

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



  • 本文链接地址:http://www.dbainfo.net/sql2005-direct-update-system-catalogs.htm
  • 本文为dbainfo个人原创,请在尊重作者劳动成果的前提下进行转载;
  • 转载务必注明原始出处 : Sybase数据库技术,数据库恢复专家
  • 对《SQL2005 如何更新系统表》有何疑问或见解,请在本文下方发表;
  • 对网站还有其他问题或建议,请提交在留言板,谢谢!
    1. 林熊熊
      2014-06-09 20:53:20


      • dbainfo
        2014-06-09 21:18:43

        SQL Server 2005之前的所有版本都是可以直接修改系统表的;
        SQL 2005及后续版本不能直接修改系统表了。
        SQL Server和Sybase数据库是同源的,sybase ASE最新版本仍然还可以修改系统表;
        先sp_configure “allow updates to system table”,1

    :wink: :twisted: :roll: :oops: :mrgreen: :lol: :idea: :evil: :cry: :arrow: :?: :-| :-x :-o :-P :-D :-? :) :( :!: 8-O 8)