This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects.  You can find a list of all of the deprecated views in the introduction post.

The compatibility view sysobjects returns one row for every object in a database.  For each object, the compatibility view returns information on the type, state, and other metadata about objects.  Objects include tables, views, stored procedures, synonyms, and etc.  For every object that can be created, a row is available in this view.

There are two catalog views that replace sysobjects.  These are sys.objects and sys.all_objects.  For the most part these two catalog views functions similar to sysobjects.  Every row represents and object and it’s properties in a database.  The chief difference between sys.objects and sys.all_objects is that while both return all user objects, the later also returns system objects.

Worthless Columns

There are a number of columns in sysobjects that provide no meaningful data whatsoever.  These columns always return either 0 or NULL.  Any business logic dependent on these columns will be seriously flawed.  The worthless columns in sysobjects are status, info, base_schema_ver, replinfo, schema_ver, stats_schema_ver, indexdel, version, seltrig, and cache.

Query Via sysobjects

With the number of worthless columns in sysobjects, a lot of the complexity of the compatibility view is removed.  There are no columns that need to be reverse engineered for queries against the view.  This results in a query such as the one in Listing 1.

--Listing 1 – Query for sys.sysobjects
SELECT name
,id
,xtype
,uid
,info
,status
,base_schema_ver
,replinfo
,parent_obj
,crdate
,ftcatid
,schema_ver
,stats_schema_ver
,type
,userstat
,sysstat
,indexdel
,refdate
,version
,deltrig
,instrig
,updtrig
,seltrig
,category
,cache
FROM sysobjects

Query via sys.objects

In the reverse, though, queries against sys.objects to mimic sysobjects do require a bit of complexity.  The main source of the complexity comes from the columns such as ftcatid and deltrig which return information from the OBJECTPROPERTY function in the compatibility view.  The other complexity with sys.objects is the *stat and category columns.  These columns function as summary columns representing the state of a number of values in the view.  Placing all of this logic into a query will produce the query in Listing 2 against sys.objects.

--Listing 2 – Query for sys.objects
SELECT
name
,object_id AS id
,type AS xtype
,NULL AS uid
,0 AS info
,0 AS status
,0 AS base_schema_ver
,0 AS replinfo
,parent_object_id AS parent_obj
,create_date AS crdate
,CASE WHEN type = 'U' THEN OBJECTPROPERTY(object_id, 'TableFulltextCatalogId') ELSE 0 END AS ftcatid
,0 AS schema_ver
,0 AS stats_schema_ver
,CASE WHEN type IN ('UQ','PK') THEN 'K' ELSE type END AS type
,CASE WHEN type IN ('S','U') THEN 1 ELSE 0 END AS userstat
,CASE WHEN type = 'S' THEN 1
WHEN type = 'V' THEN 2
WHEN type = 'U' THEN 3
WHEN type IN ('P','RF','X') THEN 4
WHEN type = 'D' THEN 6
WHEN type = 'R' THEN 7
WHEN type = 'TR' THEN 8
WHEN type IN ('PK','UQ') THEN 9
WHEN type = 'C' THEN 10
WHEN type = 'F' THEN 11
ELSE 0 END AS sysstat
,0 AS indexdel
,create_date AS refdate
,0 AS version
,CASE WHEN type = 'U' THEN OBJECTPROPERTY(object_id,'TableDeleteTrigger')
WHEN type = 'TR' then parent_object_id
ELSE 0 END AS deltrig
,CASE WHEN type = 'U' THEN OBJECTPROPERTY(object_id,'TableInsertTrigger')
ELSE 0 END AS instrig
,CASE WHEN type = 'U' THEN OBJECTPROPERTY(object_id,'TableUpdateTrigger')
ELSE 0 END AS updtrig
,0 AS seltrig
,CASE WHEN type = 'D' and parent_object_id <> 0 THEN 2048 ELSE 0 END
+ is_ms_shipped * 2
+ is_published * 32 AS category
,0 AS cache
,principal_id
,schema_id
,parent_object_id
,type_desc
,modify_date
,is_ms_shipped
,is_published
,is_schema_published
FROM sys.all_objects

Query via sys.all_objects

As mentioned in the introduction, there is a second catalog view which replaces sysobjects; which is sys.all_objects.  This second view replaces sysobjects from a column basis identically to how sys.objects functions.  From a rowset perspective, the only difference is that sys.all_objects includes system objects as well as user objects, where the other views provide only user objects.  A query demonstrating the use of sys.all_objects is provided in Listing 3.

--Listing 3 – Query for sys.all_objects
SELECT
name
,object_id AS id
,type AS xtype
,NULL AS uid
,0 AS info
,0 AS status
,0 AS base_schema_ver
,0 AS replinfo
,parent_object_id AS parent_obj
,create_date AS crdate
,CASE WHEN type = 'U' THEN OBJECTPROPERTY(object_id, 'TableFulltextCatalogId') ELSE 0 END AS ftcatid
,0 AS schema_ver
,0 AS stats_schema_ver
,CASE WHEN type IN ('UQ','PK') THEN 'K' ELSE type END AS type
,CASE WHEN type IN ('S','U') THEN 1 ELSE 0 END AS userstat
,CASE WHEN type = 'S' THEN 1
WHEN type = 'V' THEN 2
WHEN type = 'U' THEN 3
WHEN type IN ('P','RF','X') THEN 4
WHEN type = 'D' THEN 6
WHEN type = 'R' THEN 7
WHEN type = 'TR' THEN 8
WHEN type IN ('PK','UQ') THEN 9
WHEN type = 'C' THEN 10
WHEN type = 'F' THEN 11
ELSE 0 END AS sysstat
,0 AS indexdel
,create_date AS refdate
,0 AS version
,CASE WHEN type = 'U' THEN OBJECTPROPERTY(object_id,'TableDeleteTrigger')
WHEN type = 'TR' then parent_object_id
ELSE 0 END AS deltrig
,CASE WHEN type = 'U' THEN OBJECTPROPERTY(object_id,'TableInsertTrigger')
ELSE 0 END AS instrig
,CASE WHEN type = 'U' THEN OBJECTPROPERTY(object_id,'TableUpdateTrigger')
ELSE 0 END AS updtrig
,0 AS seltrig
,CASE WHEN type = 'D' and parent_object_id <> 0 THEN 2048 ELSE 0 END
+ is_ms_shipped * 2
+ is_published * 32 AS category
,0 AS cache
,principal_id
,schema_id
,parent_object_id
,type_desc
,modify_date
,is_ms_shipped
,is_published
,is_schema_published
FROM sys.all_objects

Summary

In this post, we compared the compatibility view sysobjects with the catalog views sys.objects and sys.all_objects.  The change requires some changing in how data is presented. With the number of columns that no longer provide any data, it is worth looking at where sysobjects is being used.  After reading all of this, do you see any reason to continue using sysobjects?  Is there anything missing from this post that people continuing to use the compatibility view should know?

Related posts:

1. Lost in Translation – Deprecated System Tables – sysmembers
2. Lost in Translation – Deprecated System Tables – sysindexkeys
3. Lost in Translation – Deprecated System Tables – syspermissions