1.
Compatibility Views
· System tables in SQL Server 2000 and prior
versions are implemented as compatibility views
· Sys.sysxxxx (sysxxx was system tables)
2.
Catalog Views
·
Catalog
views return information that is used by the SQL Server Database Engine.
·
Use
catalog views because they are the most general interface to the catalog
metadata and provide the most efficient way to obtain, transform, and present
customized forms of this information. All user-available catalog metadata is
exposed through catalog views.
·
Catalog
views do not contain information about replication, backup, database
maintenance plan, or SQL Server Agent catalog data
·
Sys.xxx
3.
Information Schema Views
·
ANSI/ISO
standard for providing read only views which provide an interface for
displaying the system meta data information related to tables, columns, views,
procedures etc.
·
The
purpose of having information views is to have a standard (system independent)
for portability across different relational databases.
·
Information
schema views provide very limited information about the meta data, for example
it does not provide information about non-standard or features specific to SQL
Server i.e. CLR, Service Broker, etc. So if you do not intend to port your database
to another ANSI compliant relational database, its recommended to use the
standard Catalog Views which gives you more information specific to SQL Server.
·
INFORMATION_SCHEMA.xxx.
4.
Dynamic Management Views
·
Return
internal, implementation-specific state data
·
There
are two types of dynamic management views and functions: database-scoped and server-scoped
·
Sys.dm_xxx
