Finding System Objects in SQL Server Databases

Occasionally, mistakes were made and user database objects, such as tables, stored procedures, and functions are created in master database by accident. Now the question is how to identify the system database objects and the user database objects?

In Microsoft SQL Server Management Studio, on the Object Explorer, if you expand the database tree and then the database object tree, for example tables, you can see a folder called System Tables (for Tables), System Views (for Views), etc..

You can also query the sys.objects. There is a column in there called is_ms_shipped. It is a bit flag to identify if the “object is created by internal SQL Server component”. Please see this article to get more information on the sys.objects. So to see which objects are created by internal SQL Server components (system objects) in master database, you can run the following query:

  1: USE [master];
  2: 
  3: SELECT name,
  4:      object_id,
  5:      type_desc,
  6:      create_date,
  7:      modify_date,
  8:      is_ms_shipped
  9: FROM sys.objects
 10: WHERE is_ms_shipped = 1;

Alternatively, you can also use the following query:

  1: USE [master];
  2: 
  3: SELECT name,
  4:      object_id,
  5:      type_desc,
  6:      create_date,
  7:      modify_date,
  8:      is_ms_shipped
  9: FROM sys.objects
 10: WHERE OBJECTPROPERTY(object_id, 'IsMSShipped') = 1;

Conversely, if you want to find user database objects in the master database, you can run the following query:

  1: USE [master];
  2: 
  3: SELECT name,
  4:      object_id,
  5:      type_desc,
  6:      create_date,
  7:      modify_date,
  8:      is_ms_shipped
  9: FROM sys.objects
 10: WHERE is_ms_shipped = 0;

Or:

  1: USE [master];
  2: 
  3: SELECT name,
  4:      object_id,
  5:      type_desc,
  6:      create_date,
  7:      modify_date,
  8:      is_ms_shipped
  9: FROM sys.objects
 10: WHERE OBJECTPROPERTY(object_id, 'IsMSShipped') = 0;

There is a couple caveat to this. Kalen Delaney (blog | twitter), pointed out in her blog post, that there is an undocumented stored procedure sp_MS_marksystemobject that can marked a user created system database object into system objects. Also if we turned on the allow updates configuration in master database, then any stored procedures that the user created in the master database will be marked as 1 for the IsMSShipped property.