Schema corruption may have occurred. Please run DBCC CheckCatalog.

SQL 2005 Modifying system tables

This article by www.sqlstudy.com Original, All rights reserved, reprint please indicate the author and the source!

Ways to modify system tables in SQL Server 2000 most people know that there is less information on how to modify system tables in SQL 2005, although Microsoft is not in favor of modifying system tables and hiding the code that modifies the system tables. But Microsoft has always liked to keep a small back door for itself. That's what I thought: the first person to know how to modify a SQL 2005 system table is the Microsoft SQL Server 2005 design, developer. So I went to MSDN and finally found a way to modify the system tables in SQL 2005:)

SQL 2005 modifies two prerequisites for a system table 1. Start SQL Server instance (instance) in single-user mode (Single-user modes). 2. Use an administrator-only connection (dac:dedicated Administrator connections) to connect to SQL Server Instance

Below, you will learn how to implement these two prerequisites for modifying the system tables.

1. Start SQL Server 2005 Instance (instance) in single-user mode.

"Start"--"run"--"services.msc" into Windows Service Manager.

Locate the SQL Server service--right---"Properties"

According to the "path to executable" content, mine is:

"D:\Microsoft SQL Server 2005\mssql.1\mssql\binn\sqlservr.exe"-ssqlb

command line, go to sqlservr.exe installation path, execute: sqlservr.exe-ssqlb-m

C:\>D:D:\>CD D:\Microsoft SQL Server 2005\mssql.1\mssql\binnd:\microsoft SQL Server 2005\mssql.1\mssql\binn> Sqlservr.exe-ssqlb-m

-SSQLB indicates that the SQL Server instance to start is named: the Sqlb;-m parameter indicates that the instance of SQL Server is started in single-user mode. If it goes well, the SQL Server instance starts up. If not start, you can restart the machine, and then try, if still not start, then you can according to the error content, to the Internet to find solutions.

2. Connect to SQL Server 2005 using a DAC.

The most common way to connect to SQL Server 2005 with a DAC is to execute sqlcmd with the-A parameter at the command line, such as:

C:\sqlcmd-E-S Mypc\sqlb-a

You can also use the SQL Server Super User and password to enter:

C:\sqlcmd-U sa-p * * * mypc\sqlb-a

Note: This is "mypc\sqlb" (computer_name\instance_name), not just the SQL Server instance name "Sqlb". Tip: In addition to the default instance, all instances of the database engine are identified by the instance name specified during the installation of the instance. The application must provide the name of the computer that is ready to connect and the instance name of the named instance. The computer name and instance name are specified in the format computer_name\ instance_name.

If you cannot connect to an instance of SQL Server 2005 through a DAC, you will have to check that SQL Server Browser this service started. SQL Server Browser listens on port 1434 (UDP port), which can direct the client to establish the correct connection by returning the appropriate IP and port based on the instance name sent by the client.

In most cases, the DAC connection can be completed successfully. However, you do not feel very painful to operate SQL Server under the command line, and if you do not configure a CMD environment, the results are always messy. Oh, I'm here to explain how to use the SQL Server Management Studio (SSMS) DAC to SQL 2005. The DAC connection is not the same as a normal connection, it has its own dedicated port, and as long as we find the DAC port, we can connect to the SQL 2005 instance in any way (command line or SSMS).

How do I find the dedicated port for the DAC? When you start SQL Server under a command line user, SQL Server prints a lot of log information on the screen. The key is to find the content that contains "dedicated admin connection":

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

This message tells us that SQL 2005 listens to the client's DAC connection on the 1183 port on the network address 127.0.0.1. We started SSMS and entered in the server name: 127.0.0.1,1183. Here the IP and port in the middle of the comma "," to separate. Of course, you can also establish a DAC connection by using sqlcmd under commands.

C:\sqlcmd-E-S 127.0.0.1,1183

It is important to note that if a DAC port is specified, do not add the-A parameter after sqlcmd, otherwise an error occurs. At this time, there is no need to start SQL Server Browser, as we have told sqlcmd to connect to the DAC address and port is: 127.0.0.1,1183.

can be connected to SQL 2005 via a DAC, an error occurs, ignoring it.

3. Modify the system tables in SQL 2005 use Mastergocreate table DDD (ID int. NOT NULL) Goinsert into DDD values (Ten) go

At this point, we created a table: DDD. The following is a query for the metadata for this table "DDD" in SQL 2005 system table SYS.SYSSCHOBJS. SYS.SYSSCHOBJS is similar to the system table Dbo.sysobjects in SQL 2000.

SELECT * from SYS.SYSSCHOBJS where name = ' DDD '

The result set listed below, because of the layout, I omitted created, modified two date field contents.

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

At this time, I would like to rename the table "ddd" to "Sqlstudy":

Update SYS.SYSSCHOBJS Set name = ' Sqlstudy ' WHERE name = ' DDD ' Warning: system table ID 34 in database ID 1 has been updated directly, but cache consistency may not be maintained. You should restart SQL Server. (1 rows affected)

Because there is a cache that causes inconsistencies, the new table name may not take effect immediately, and at the command line CTRL + C, restart SQL Server 2005. You can see that the table "DDD" has been renamed "Sqlstudy".

SELECT * FROM Sqlstudyid-------10 supplemental Content: View statements for SQL 2005 system tables. Select name from sys.all_objects where type = ' S ' ORDER by NameName-------------------Sysallocunitssysasymkeyssysbinobjssysbinsubobjssyscertssyschildinstssysclsobjssyscolparssyscon Vgroupsysdbfilessysdbregsysdercvsysdesendsysendptssysfiles1sysftindssysguidrefssyshobtcolumnssyshobtssysidxstatssysiscols syslnklgnssyslogshipperssysmultiobjrefssysnsobjssysobjkeycryptssysobjvaluessysownerssysprivssysqnamessysremsvcbindssysrmt Lgnssysrowsetcolumnssysrowsetrefssysrowsetssysrtssysscalartypessysschobjssysserefssyssingleobjrefssyssqlguidessystypedsub Objssysusermsgssyswebmethodssysxlgnssysxmitqueuesysxmlcomponentsysxmlfacetsysxmlplacementsysxpropssysxsrvs

In the SQL Server 2005 master database, there are 51 system tables. And the schema of these system tables is "SYS".

This article, "SQL 2005 modifies system table methods" example, runs through the SQL Server 2005 Enterprise Edition SP2 (9.00.3042.00) environment. Operating system: Windows Server 2003.

This article references: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=89594&SiteID=1

Schema corruption may have occurred. Please run DBCC CheckCatalog.