sql-server - SQL Server sys.databases 与 sysdatabases？

Question

我有一个用于列出数据库的查询:

SELECT * FROM sys.databases

但是这个查询在 SQL Server 2000 中不起作用。

以下查询适用于 SQL Server 2000 及更高版本:

SELECT NAME FROM sysdatabases;

我找到了this article其中有一些关于 sys.database 的注释(并且还指定出于兼容性原因保留了 sysdatabases)。

我想知道的是，是否有任何原因我不应该使用 sysdatabases 来获取数据库名称列表？除了使用 sys.databases 是较新的样式之外，我没有在任何地方看到任何东西。

Answer 1

我认为有以下原因:

• 可能是removed in future versions (强调我的):

  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.

  换句话说:是的，您可以使用sysdatabases，但是一旦发布不支持sysdatabases的SQL Server版本，您可能需要重写所有内容 不再。

• 兼容性 View (例如 sysdatabases)在 SQL Server 2000 中“保持原样”，并且没有获得新功能。因此，它们包含 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.