Skip navigation

How can I amend the system tables in SQL Server?

A. First, it should be said that unless you are using code that Microsoft have published then any direct updates to the system tables are not supported - so if you're not sure either don't do it, or take a backup first.

With the exception of "materialised" tables like syslocks and sysprocesses you can pretty much use standard SQL commands to update the system tables. 

As far as triggers go, you can define triggers on system tables, however SQL Server updates most system tables internally via it's own api's - only some get done via standard TSQL. Only updates done via standard TSQL result in a trigger firing, however there is no list of what tables this does work with, and it certainly wouldn't be supported if you did use it.

To update system tables first you have to set the "allow updates" flag as follows :-

sp_configure 'allow updates',1
go
reconfigure with override
go

Then go can make updates - make sure you always do this within a transaction so that if you affect more rows than you meant to then it can be rolled back. Afterwards reset the 'allow updates' flag.

sp_configure 'allow updates',1
go
reconfigure with override
go

There are also a few occasions where Microsoft have put in "extra" protection to stop users mucking about. One example is sysindexes where you must specify the name, id and indid in the where clause otherwise the update will fail.


TAGS: SQL
Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish