Deprecated System Tables: Replacing sysobjects and sysindexes

Deprecated system tables like sysobjects and sysindexes still answer your queries, but the answers no longer mean what they used to. Replace them with the current catalog views, and check the units before you trust a single number.

Why old system tables still run

Here is a story I have seen more than once. A maintenance script written many years ago reports the size of every table. It runs happily on a new server. Then a developer says the numbers look too small. Nothing failed, so nobody looked.

The reason is simple. sysobjects and sysindexes survive only as compatibility views. They were built for a time when a table was one chunk of data pages. Today a table can have large-value storage, partitions and several index types. The old views cannot describe all of that.

Let me build one small table and ask the old and new views the same questions.

Replace sysobjects with sys.objects

The table has an int key and an nvarchar(max) column holding 10,000 characters. That is about 20 KB in one row, so it must go to large-value storage. Run this in any test database.

DROP TABLE IF EXISTS dbo.CatalogDemo;

CREATE TABLE dbo.CatalogDemo (Id int PRIMARY KEY, Payload nvarchar(max));

INSERT dbo.CatalogDemo (Id, Payload)
VALUES (1, REPLICATE(CAST(N'x' AS nvarchar(max)), 10000));

SELECT name, xtype FROM sys.sysobjects WHERE id = OBJECT_ID(N'dbo.CatalogDemo');
SELECT name, type_desc FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.CatalogDemo');

Both queries find the table. The old one calls its type U. The new one says USER_TABLE. Same object, but the new column names are readable and the code is a lot easier to hand to the next person. This part of the move is mostly renaming.

Count pages with the right view

This is where the old report goes wrong. The legacy view has a column called dpages. The modern partition view splits pages into in-row data, large-value pages and total used pages. They are different measures.

SELECT name, dpages AS LegacyDataPages, used AS LegacyUsedPages
FROM sys.sysindexes
WHERE id = OBJECT_ID(N'dbo.CatalogDemo') AND indid < 255
ORDER BY indid;

SELECT i.name,
       SUM(p.in_row_data_page_count) AS InRowDataPages,
       SUM(p.lob_used_page_count)    AS LobUsedPages,
       SUM(p.used_page_count)        AS TotalUsedPages
FROM sys.indexes AS i
JOIN sys.dm_db_partition_stats AS p
  ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.CatalogDemo')
GROUP BY i.name
ORDER BY i.name;

The old view reports 1 data page and 2 used pages. The new view reports 1 in-row page, 4 large-value pages and 6 pages used in total. So a report built on dpages sees 1 page for a table that really uses 6. That is how a size report ends up too small without raising any error.

Do not try to force the two to match. Decide what the report is for. If it should show total space, use the total. If it should show row data only, use the in-row column and say so in the heading. Group the partition rows by index so a partitioned table is not counted twice.

Find who still calls the old views

Before you change anything, find the callers. SQL Server counts every use of a deprecated feature in a performance counter. The next block reads the counter, runs one legacy query, and reads the counter again.

DROP TABLE IF EXISTS #Before;

SELECT cntr_value AS Uses
INTO #Before
FROM sys.dm_os_performance_counters
WHERE object_name LIKE N'%Deprecated Features%' AND instance_name = N'sysobjects';
GO
SELECT COUNT(*) AS LegacyRows FROM sys.sysobjects;
GO
SELECT c.cntr_value - b.Uses AS NewUses
FROM sys.dm_os_performance_counters AS c
CROSS JOIN #Before AS b
WHERE c.object_name LIKE N'%Deprecated Features%' AND c.instance_name = N'sysobjects';

The counter went up by 1 for my single query. That tells you the old view is still in use on your server. It does not tell you who is calling. For that, search your stored procedure text, Agent job steps and the scripts your team keeps for the old names. A match can be a comment, so read before you edit.

Keep the report’s contract

Keep the same column names and units for whoever reads the report, unless you tell them about a change. Test with a heap, a partitioned table and a table with large values. Also run the new query under the account the scheduled job uses. Metadata visibility depends on permissions, and an administrator can see objects that a restricted login cannot. Replace one dependency at a time, and compare old and new output before you switch. Then clean up the demo table.

DROP TABLE IF EXISTS dbo.CatalogDemo;
DROP TABLE IF EXISTS #Before;

Next time a report runs without errors, check what it is really measuring.

A query that still runs is not a query that still works, it is an old promise.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.