CODEWITHSUNDEEP
sql-server
Kyle
Kyle

Reputation: 17677

SQL Server sys.databases vs sysdatabases?

I have a query which I am using to list databases:

SELECT * FROM sys.databases

But this query does not work in SQL Server 2000.

The following query works in both SQL Server 2000 and above:

SELECT NAME FROM sysdatabases;

I've found this article which has some notes on sys.database (and also specified that sysdatabases has been kept around for compatibility reasons).

What I'm wondering, is if there is any reasons I should NOT use sysdatabases to get a list of database names? I haven't seen anything anywhere other than using the sys.databases is the newer style.

Upvotes: 6

Views: 12999

Answers (2)

Heinzi
Heinzi

Reputation: 172220

I can think of the following reasons:

  • It might be removed in future versions (emphasis mine):

    This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

    In other words: Yes, you can use sysdatabases, but you might need to rewrite everything once a SQL Server version is released which does not support sysdatabases any more.

  • The compatibility views (such as sysdatabases) are kept "as they were" in SQL Server 2000 and don't get new features. Thus, they contain less information than their new counterparts:

    The compatibility views expose the same metadata that was available in SQL Server 2000. However, the compatibility views do not expose any of the metadata related to features that are introduced in SQL Server 2005 and later. Therefore, when you use new features, such as Service Broker or partitioning, you must switch to using the catalog views.

Upvotes: 7

Devart
Devart

Reputation: 121912

SELECT name FROM sysdatabases

after it check - 

SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Deprecated Features%'
    AND instance_name = 'sysdatabases'
    AND cntr_value > 0

and read about deprecated objects

short answer: if possible try to use objects only from sys schema -

SELECT * FROM sys.databases

Upvotes: 2

Related Questions