SA0205 : The backward compatibility views for SQL Server 2000 system tables are deprecated. Use the current SQL Server system views instead

Introduction

Using outdated compatibility views from SQL Server 2000 system tables poses a risk of future compatibility issues, as these views will be deprecated in upcoming SQL Server releases.

Description

Using backward compatibility views from SQL Server 2000 system tables can lead to problems as these views are scheduled for removal in future SQL Server versions. Such views offer access to metadata in a format suited for SQL Server 2000, but newer versions demand modern alternatives.

For example:

-- Example of deprecated view usage
SELECT * FROM sys.sysobjects;

This query relies on the backward compatibility view sys.sysobjects , which is based on SQL Server 2000 system tables. Modern SQL Server versions prioritize using updated dynamic management views (DMVs) and catalog views for better performance, security, and compliance with current features.

Using outdated views can cause scripts to break in future migrations to newer SQL Server versions.
Reliance on deprecated features can inhibit leveraging new SQL Server capabilities, affecting performance and maintainability.

How to fix

Replace outdated compatibility views from SQL Server 2000 system tables with modern SQL Server system views to ensure future compatibility and enhanced performance.

Follow these steps to address the issue:

1.Identify queries using deprecated views like sys.sysobjects . Modify these queries to use current system views or dynamic management views (DMVs).

2.Consult the SQL Server System Catalog Views documentation to find equivalent views in modern SQL versions that offer similar functionality.

3.Update your queries by replacing deprecated views with their modern counterparts. For instance, replace sys.sysobjects with sys.objects .

For example:

-- Old usage with a deprecated view
SELECT * FROM sys.sysobjects;

-- Updated usage with a modern view
SELECT * FROM sys.objects;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

20 minutes per issue.

Additional Information

Deprecated Database Engine Features in SQL Server 2017

Example Test SQL

select * from sysservers

select * from sys.sysservers
select * from someTable

Analysis Results

	Message	Line	Column
1	SA0205 : The backward compatibility views for SQL Server 2000 system tables are deprecated. Use the current SQL Server system views instead.	1	14
2	SA0205 : The backward compatibility views for SQL Server 2000 system tables are deprecated. Use the current SQL Server system views instead.	3	18

Analysis Rules