Friday, February 16, 2007

sp_MS_marksystemobject - When an undocumented stored procedure helped me

During a maintenance of a system that used merge replication, I dropped and recreated the MSmerge_tombstone table which is used by replication.

After a period, the client sent me an email asking me if he can safely drop this table as he did not find it relevant to his data.
this was because the he used to hide system objects shown in SQL Server 2000 Enterprise manager. And when I recreated the table it was not marked as system.

I tried to discover where does SQL Server save the "system" property in system tables, but I've got no clear results.

After a search I found an undocumented stored procedure "sp_MS_marksystemobject" that did the work.
It uses some bit manipulation to the status field in sysobjects table to mark an object as "system"... Something you can't discover by luck.

The syntax for using it is :
sp_MS_marksystemobject 'object_name'

You can use sp_helptext sp_MS_marksystemobject to check the code.


Anonymous said...

do you know maybe a way how to unmark it?

Ba3 said...

No funciona en SQL Server 2008

Hesham A. Amin said...

@Ba3, It was undocumented. not sure if there is an alternative.