SQL Server System Views

they expose database metadata.

Catelog views:

Catalog views return information that is used by the SQL Server Database Engine. All user-available catalog metadata is exposed through catalog views.

some examples:

sys.databases, sys.database_files,sys.master_files

sys.linked_logins, sys.remote_logins,sys.servers

sys.objects, sys.tables, sys.indexes,sys.views, sys.procedures, sys.partitions,sys.schemas

Dynamic Management views and functions:

Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. Dynamic management views and functions in future releases may not be compatible with the dynamic management views and functions in this release.

All dynamic management views and functions exist in the sys schema and follow this naming convention dm_*. you must prefix the name of the view or function by using the sys schema.

some examples:

sys.dm_exec_connections, sys.dm_exec_sessions, sys.dm_exec_sql_text

sys.dm_tran_database_transactions

Compatibility views:

Many of the system tables from earlier releases of SQL Server are now implemented as a set of views. These views are known as compatibility views, and they are meant for backward compatibility only. 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.

some examples:

you can use sys. prefix in front of the sysxxx.

sysusers, sysprocesses, sysobjects,syscolumns, sysdatabases, sysservers

Replication Views:

views are available in different related databases: msdb, distribution, publication, subscription databases.

Information Schema Views:

One of several methods SQL Server provides for obtaining metadata. it comply with ISO standard definition of INFORMATION_SCHEMA. Good for generic purpose DB tools to use.

Information schema views are defined in a special schema named INFORMATION_SCHEMA. This schema is contained in each database. Each information schema view contains metadata for all data objects stored in that particular database