Menu
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.
518
“System Datebase” (SysDB) is an Open Source daemon which aggregates system management and inventory information and stores them in a central database. All information is retrieved from other hardware or software systems and may be queried through a generic interface.
Database schemas act as namespaces or containers for objects, such as tables, views, procedures, and functions, that can be found in the sys. objects catalog view. Each schema has a an owner. The owner is a security principal.
When you detach a database, SQL cleanly shuts it down and then removes any reference to the DB. A clean shutdown involved committing or rolling back all transactions, writing all dirty pages to disk and then writing an entry into the transaction log.
All sys objects are stored in resource database and are logically visible under sys schema of each database.
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.
174
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
