Although
you were allowed to see data in the system tables in versions of SQL Server
before 2005, you weren't encouraged to do this. Nevertheless, many people used
system tables for developing their own troubleshooting and reporting tools and
techniques, providing result sets that aren't available using the supplied system procedures.
Some of the compatibility views have names that
might be quite familiar to you, such as sysobjects, sysindexes, sysusers, and sysdatabases.
Others, like sysmembers and sysmessages, might
be less familiar. For compatibility reasons, the views in SQL Server 2008 have
the same names as their SQL Server 2000 counterparts, as well as the same
column names, which means that any code that uses the SQL Server 2000 system
tables won’t break. However, when you select from these views, you are not
guaranteed to get exactly the same results that you get from the corresponding
tables in SQL Server 2000. In addition, the compatibility views do not contain
any metadata related to new SQL Server 2005 or 2008 features, such as partitioning .
SQL Server 2005 and 2008
also provide compatibility views for the SQL Server 2000 pseudotables, such as sysprocesses and syscacheobjects. Pseudotables are tables that are not based on
data stored on disk but are built as needed from internal structures and can be
queried exactly as if they are tables. SQL Server 2005 replaced these
pseudotables with Dynamic Management Objects. Note that there is not always a
one-to-one correspondence between the SQL Server 2000 pseudotables and the SQL
Server 2005 and SQL Server 2008 Dynamic Management Objects.
For example, for
SQL Server 2008 to retrieve all the
information available in sysprocesses, you
must access three Dynamic Management Objects: sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests.
