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 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'.
SELECT *
FROM sys.objects
WHERE 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?
CREATE TABLE tiny
(col1 int);
GO
EXEC sp_MS_marksystemobject 'tiny';
GO
SELECT *
FROM sys.objects
WHERE 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;
reconfigure;
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;
GO
CREATE PROC today
AS SELECT getdate();
GO
SELECT *
FROM sys.objects
WHERE is_ms_shipped = 1 AND name LIKE 't%';
Have fun!
~Kalen