Is_ms_shipped Property in SQL Server

Most of the time, we need to search for objects using metadata (data about data) to get information about SQL Server. For example, we use a query to find which version of SQL server is running on a particular machine. Metadata helps us to find settings and configurations for various objects in our database.

In this example, we need to find on which date the table ‘Customer’ was created. We need to query SQL Server metadata in order to find this date.

SELECT create_date 
FROM   sys.objects
WHERE  name = 'Customer';

sys.objects is a table that SQL Server created which acts as metadata.

When we installed SQL Server for the first time, it automatically created certain tables, procedures, and various objects to hold metadata that is considered as “shipped” items by the software provider Microsoft with SQL server.

When we create a new database under SQL server, it automatically creates some objects to hold metadata.

is_ms_shipped Property in SQL Server

The is_ms_shipped property in SQL server shows which objects were created (shipped) by Microsoft when we installed SQL Server and which objects were created by users.

SELECT name, 
       object_id, 
       is_ms_shipped 
FROM   sys.objects;

is_ms_shipped = 1 (indicates this object was shipped or created by Microsoft)
is_ms_shipped = 0 (indicates this object was created by a user)

Using the query above, we see various objects created under a database. The column “is_ms_shipped” indicates whether an object was created by a user or was shipped by Microsoft.

Now we know that we have created the STUDENTS table, the column list shows the object name, the object_id (which is unique for every created object in SQL server), and the is_ms_shipped column.

Using the ObjectProperty() system created function, we can find out whether a particular object was shipped by Microsoft or was created by a user.

--2105058535 is object_id for table students
select CASE WHEN OBJECTproperty(2105058535,'IsMSShipped')= 0 
            THEN 'Created by User' 
       ELSE 'Shipped by Microsoft' 
       END AS 'IsMsShipped';

--2089058478 is object_id for syscommittab table

SELECT CASE WHEN OBJECTproperty(2089058478,'IsMSShipped')= 0 
     THEN 'Created by User' 
       ELSE 'Shipped by Microsoft' 
       END AS 'IsMsShipped';

If we want to make a list of objects created by Microsoft or created by users for auditing purposes, we can use the query below.

--is_ms_shipped = 1 indicates objects shipped/created by Microsoft.
SELECT name, object_id, is_ms_shipped 
FROM   sys.objects
WHERE  is_ms_shipped = 1;

--is_ms_shipped = 0 indicates objects created by users.
SELECT name, object_id, is_ms_shipped 
FROM   sys.objects
WHERE  is_ms_shipped = 0;