Deep dive to Sql Server 2005 system tables!

WARNING: These kind of system table modification activities are NOT SUPPORTED by Microsoft! So if you try these activities that means you accept not to get support for your Sql Server 2005 installation from Microsoft. You are left alone with the circumstances of your activities. Use at your own risk!

Here is a great trick explained to update system tables in Sql Server 2005!
SQL Server 2005 – Ad hoc updates to system catalogs are “allowed”

also You may check the following thread:
MSDN Forum Thread Link

USE SampleSite_productcatalog

— Execute below statements and then restart the server in singleuser mode
sp_configure ‘allow updates’, 1
reconfigure with override

— To start a named instance of SQL Server in single-user mode from a command prompt
— From a command prompt, enter the following command:
— sqlservr.exe -m -s INSTANCENAME
MSDN Reference

— Connect to the server using Dedicated Admin Connection – DAC
— OPEN Management Studio and connect to “ADMIN:SERVERNAMEINSTANCENAME” in your query window

— below queries shows a sample scnerio for updating system tables.

— shows the path of the ftCatalog
sp_help_fulltext_catalogs [ @fulltext_catalog_name = ] ‘fulltext_catalog_name’

— show the quesy for the view
SELECT * FROM sys.fulltext_catalogs
DELETE FROM sys.fulltext_catalogs WHERE fulltext_catalog_id=6 –View or function ‘sys.fulltext_catalogs’ is not updatable because the modification affects multiple base tables.
sp_helptext ‘sys.fulltext_catalogs’ — shows the query about following tables: sys.sysclsobjs , sys.sysobjvalues , sys.syssingleobjrefs
SELECT * FROM sys.database_files
sp_helptext ‘sys.database_files’
SELECT * FROM sys.database_files WHERE FILE_ID=65537
SELECT * FROM sys.sysclsobjs WHERE class=32 AND id=6
SELECT * FROM sys.sysobjvalues WHERE valclass=41 AND objid=6 AND subobjid = 0 and valnum = 0 –SVC_FTCATPATH
SELECT * FROM sys.syssingleobjrefs WHERE depid = 6 — SRC_FTCATOWNER
DELETE FROM sys.syssingleobjrefs WHERE depid = 6 — Warning: System table ID 74 has been updated directly in database ID 17 and cache coherence may not have been maintained. SQL Server should be restarted.
SELECT * FROM sys.sysobjvalues WHERE valclass=41 AND objid=6 — AND subobjid = 0 and valnum = 0 –SVC_FTCATPATH
DELETE FROM sys.sysobjvalues WHERE valclass=41 AND objid=6 — AND subobjid = 0 and valnum = 0 –SVC_FTCATPATH — Warning: System table ID 60 has been updated directly in database ID 17 and cache coherence may not have been maintained. SQL Server should be restarted.
DELETE FROM sys.sysclsobjs WHERE class=32 AND id=6 — Warning: System table ID 64 has been updated directly in database ID 17 and cache coherence may not have been maintained. SQL Server should be restarted.
SELECT * FROM sys.sysdbfiles WHERE fileid=65537
DELETE FROM sys.sysdbfiles WHERE fileid=65537 — Warning: System table ID 76 has been updated directly in database ID 17 and cache coherence may not have been maintained. SQL Server should be restarted.

SELECT * FROM sys.database_files

Brief Summary of the operations below:
————————————————————————————-
1 Attach SampleSite_ProductCatalog db
2 Delete all the FullTextCatalogs you can…
3 Delete all the users except the ones that are crucial such as: dbo
4 Switch to SingleUserMode and establish Direct Admin Connection
5 Using below queries you may delete the “cannot be deleted” fulltextCatalogs.
— SCRIPT BEGIN
USE [YemekSepeti_productcatalog];
DELETE FROM sys.fulltext_catalogs WHERE fulltext_catalog_id=6 — ERROR: View or function ‘sys.fulltext_catalogs’ is not updatable because the modification affects multiple base tables.
— SELECT * FROM sys.sysclsobjs WHERE class = 32
DELETE FROM sys.sysclsobjs WHERE class = 32 AND ID=6 — OK: Warning: System table ID 64 has been updated directly in database ID 17 and cache coherence may not have been maintained. SQL Server should be restarted.
DELETE FROM sys.sysobjvalues WHERE valclass = 41 AND objid=6 AND subobjid=0 AND VALNUM=0 — OK: Warning: System table ID 60 has been updated directly in database ID 17 and cache coherence may not have been maintained. SQL Server should be restarted.
— SKIPPED: SELECT * FROM sys.sysobjvalues v WHERE v.valclass = 41 AND v.objid = 6 AND v.subobjid = 0 and v.valnum = 0 –SVC_FTCATPATH
DELETE FROM sys.syssingleobjrefs WHERE depid = 6 AND class = 45 AND depsubid = 0 — OK: Warning: System table ID 74 has been updated directly in database ID 17 and cache coherence may not have been maintained. SQL Server should be restarted.
DELETE FROM sys.syssingleobjrefs WHERE depid = 6 AND class = 46 AND depsubid = 0 — OK: Warning: System table ID 74 has been updated directly in database ID 17 and cache coherence may not have been maintained. SQL Server should be restarted.
DELETE FROM sys.syssingleobjrefs WHERE depid = 6 AND class = 16 AND depsubid = 0 — OK: Warning: System table ID 74 has been updated directly in database ID 17 and cache coherence may not have been maintained. SQL Server should be restarted.
— All FULLTEXT CATALOGs are being DELETED!
— select * from sys.sysdbfiles where filetype=4
DELETE FROM sys.sysdbfiles where filetype=4 — OK: (12 row(s) affected) Warning: System table ID 76 has been updated directly in database ID 17 and cache coherence may not have been maintained. SQL Server should be restarted.
— Thus MySampleCatalog_FullTextCatalog has been deleted.
— SCRIPT END
6 SWITCH to MULTI_USER Mode. Delete the remaining user that was the owner of the deleted catalog.
7 DB detach
8 DB attach
9 DBCC CHECKDB
10 DBCC CHECKCATALOG
————————————————————————————-