THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: System Objects

As you might know, metadata is one of my favorite topics, and I've written quite a bit about metadata and system objects. A very frequent question is: What exactly IS a system object?

There are quite a few different definitions you could use. You could say a system object is one that was installed with your SQL Server installation.

You could say it is one that starts with a special prefix, like 'sys' for objects if you're using SQL Server 2000 or earlier, or one in the sys schema if you're using SQL Server 2005.

You could say a system object is one that has an object id of less than 100.

In the old SQL 2000 Enterprise Manager, you could look at a list of objects in a database, and there was a column called 'type' with a value of either 'system' or 'user'. 

The objectproperty function has an argument called 'IsSystemTable', that will show you which tables are system tables, but won't do anything for other system objects.

The objectproperty function also has an argument called 'IsMSShipped', which corresponds to a column in the SQL 2005 metadata view sys.objects called 'is_ms_shipped'.

FROM sys.objects
OBJECTPROPERTY(object_id, 'IsMSShipped') = 1;

But just because something is shipped by Microsoft, is it really a system object? There is an undocumented procedure called sp_MS_marksystemobject, that would change the properties of any object you created so that its 'is_ms_shipped' property would also show a value of 1. Does that make it a system object, just because you marked it as such?

(col1 int);
EXEC sp_MS_marksystemobject 'tiny';
FROM sys.objects
OBJECTPROPERTY(object_id, 'IsMSShipped') = 1;

I usually think of system objects as ones that have special behavior. Procedures in the master database, that start with sp_ are special, in that you can access them from anywhere. It turns out that you can create views and tables in master, with names starting with sp_, and they will also be accessible from any database, without fully qualifying the name.

System tables have always had special behavior, in that you couldn't update them without setting a configuration option for the whole server. Once you set 'allow updates' to 1, you could update system tables, in versions before SQL 2005. Metadata changed completely in SQL 2005, and I thought for a long time that the 'allow updates' configuration option  didn't do anything anymore. But I just recently found out that is not true. There is something special that 'allow updates' allows.

If you run the following, you will be given an error message:

sp_configure 'allow updates', 1;

Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.

So it tells you to RECONFIGURE, but when you do, you still get an error. However, if you run RECONFIGURE WITH OVERRIDE, you get  no error:

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

It turns out that you if you set 'allow updates' to 1, any procedure that you create in the master database will automatically be marked as 'is_ms_shipped'. You can observe this either with the OBJECTPROPERTY function or the is_ms_shipped column in sys.objects. This doesn't seem to work for tables or views, and it doesn't give the procedure any special behavior, but it changes a internal status bit to make the object seem like something special.

USE master;
AS SELECT getdate();
FROM sys.objects
is_ms_shipped =  1 AND name LIKE 't%';

Have fun!


Published Sunday, August 10, 2008 11:11 PM by Kalen Delaney
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Henning PJ said:

Great finding :-)

I think there is one special behaviour that is granted to a stored procedure that is marked as a systm procedure.

If the procedure resides in the master database with the sp_ prefix and is called from another database without using the master.. prefix, then the procedure will execute in the context of the calling database.

If the procedure is not a system object (default for user procs in master in SQL2000 and SQL2005 with allow updates =0) the context will be the master database.

This is useful if you want to write a generic stored procedure that can be called from any database and for example accessing the user daabase's sys.sysfiles.

August 12, 2008 3:22 AM

Shams said:

Hello Kalen,

Seems I have a similar situation. I had allow updates on and there are a few stored procedures which when created have been marked as ms shipped. Is there a way to reverse the change ?

Does the sp_MS_marksystemobject expects different parameters to do so.

I tried the sp_helptext to check but no avail. Thanks

December 9, 2008 1:48 PM

Kapil said:

can we add system object(stored procedure) with schema?

instead of CREATE PROC today if i create CREATE PROC [dt].today

so is it possible?

April 14, 2009 9:18 AM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement