Do we have possibility to update system tables in SQL..

Question

Do we have possibility to update system tables in SQL..

mohan.bndr

SSCrazy

Points: 2360
More actions
August 27, 2013 at 12:43 am

#279094

Can i have one clarification on system tables for SQL, Is it possible to update system tables in SQL server... Please help me out here !!!!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 1

I don't believe you can anymore. Part of the reason is because a lot of people really messed up their systems by updating system tables. What is it that you're trying to accomplish?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RandomEvent SSCrazy Points: 2963 More actions · Answer 2

Most of the system information is now held in the Resource DB which is hidden read only (for good reason) you can't even do a SQL backup of this database.

So depending on what you require to update it is unlikely depending on whether itor not it resides here.

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 3

Both good explanations above. In SQL 2000 and prior, you could. AFAIK, you can't know, precisely because most of the "tables" you query are actually views, not tables. They are materialized for your queries.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Jason.Reeves (8/27/2013)
Most of the system information is now held in the Resource DB which is hidden read only (for good reason) you can't even do a SQL backup of this database. .

What's in the system resource DB is the definitions of the various system views, functions, procedures, etc. Not actual system information. That's either in master (for server-wide information) or in the user databases (for database specific information)

The point of the resource DB is so that it can be replaced when the server is patched, which would not be possible if it had system information in it.

Edit: and no, you cannot update the system tables, there's no reason for you to need to.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 5

@mohan.bndr,

I'd still like to know what you're trying to do and why you think updating the system tables is a good idea. Depending on what you're trying to do, there might be a reasonable work around but we have to know what it is that you're trying to do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TomThomson SSC Guru Points: 104773 More actions · Answer 6

GilaMonster (8/27/2013)
Jason.Reeves (8/27/2013)
Most of the system information is now held in the Resource DB which is hidden read only (for good reason) you can't even do a SQL backup of this database. .
What's in the system resource DB is the definitions of the various system views, functions, procedures, etc. Not actual system information. That's either in master (for server-wide information) or in the user databases (for database specific information)
The point of the resource DB is so that it can be replaced when the server is patched, which would not be possible if it had system information in it.
Edit: and no, you cannot update the system tables, there's no reason for you to need to.

If you ever wrote a tool to script maintenance plans in SQL 2000 you would probably disagree with yourself - it couldn't be done without updating system tables. I don't know if things are better in that respect in later versions of SQL Server - maybe now you don't need it for that.

Of course not being able to update catalog data using ordinary DML is a violation of the relational model, so maybe MS made a mistake there; perhaps it would have been better to put enough domain constraints on the system tables, and other check constraints, and so on to ensure that although system data could be updated it wasn't possible to do anything utterly stupid with it. But it probably doesn't matter because of Codd's 12 (actually 13) rules MS SQL Server already violated rules 0,2,3,6,9 and 12 so adding rule 4 to those six made little difference. Of course rule 6 is perhaps somewhat debatable, because it's over-generalised, but being unable to update a view which uses more than one base table just because it uses more than one base table is a clear violation of what Codd said the rule should be when he realised that he original version was unachievable.

Tom

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

L' Eomot Inversé (8/27/2013)
If you ever wrote a tool to script maintenance plans in SQL 2000 you would probably disagree with yourself - it couldn't be done without updating system tables.

Quite a few things in SQL 2000 required directly manipulating the system tables and it was possible in SQL 2000 (and I did it a number of times). However what I said is in regard to SQL 2005 and later, where there's no good reason to want to manipulate the system tables and said system tables are not even visible.

btw, the maintenance plan related tables in MSDB are not system tables. They're user tables and they can be directly modified as much as you like.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass