SQL
Server introduced a set of catalog views as a general interface to the
persisted system metadata. All the catalog views (as well as the Dynamic
Management Objects and compatibility views) are in the sys schema, and you must reference the schema name when you access
the objects. Some of the names are easy to remember because they are similar to
the SQL Server 2000 system table names. For example, there is a catalog view
called objects in the sys schema, so to reference the
view, the following can be executed:
SELECT * FROM sys.objects;
Similarly,
there are catalog views called sys.indexes and sys.databases, but the columns displayed for these catalog views are very
different from the columns in the compatibility views. Because the output from
these types of queries is too wide to reproduce, let me just suggest that you
run these two queries yourself and observe the difference:
SELECT * FROM sys.databases; SELECT * FROM sysdatabases;
The sysdatabases
compatibility view is in the sys schema, so you can
reference it as sys.sysdatabases. You can also reference it using dbo.sysdatabases. But again, for compatibility reasons, the schema
name is not required, as it is for the catalog views. (That is, you cannot
simply select from a view called databases; you
must use the schema sys as a prefi x.)
When
you compare the output from the two preceding queries, you might notice that
there are a lot more columns in the sys.databases
catalog view. Instead of a bitmap status fi eld that needs to be
decoded, each possible database property has its own column in sys.databases. With SQL Server 2000, running the system procedure sp_helpdb decodes all these database options, but because sp_helpdb is a procedure, it is diffi cult to fi lter the results. As a view,
sys.databases can be queried and fi ltered. For example, if you
want to know which databases are in simple recovery mode, you can run
the following:
SELECT name FROM
sys.databases WHERE recovery_model_desc = 'SIMPLE';
