Catalog views return information that is used by the Microsoft SQL Server 2005 Database Engine. You can make use of these 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. Catalog views do not contain information about replication, backup, Database Maintenance Plan, or SQL Server Agent catalog data. Lets discuss some of the commonly used catalog views in SQL2005.
Server-wide Configuration Catalog Views
This catalog view will provide you information about server wide configuration details which is similar to executing sp_configure procedure, here you have description column which will be helpful to know what exactly the option is doing.
The sys.traces catalog view contains the current running traces on the system. This view is intended as a replacement for the fn_trace_getinfo function.
Databases and Files Catalog Views
This view will provide you information about all the databases, even it has a column for recovery model. log_reuse_wait column will be useful when you are unable to shrink log file, this column will show what is access log file. This view is similar to sysdatabases system table with additional information
This catalog view will provide enough information about database’s physical file settings. This view is similar to sysaltfiles system table in SQL2000.
The sys.database_files view will provide information about the working database. This is similar to sp_helpdb stored procedure.
Object Catalog Views
List out all the columns declared for tables and views.
List out all the indexes created on the database, contains one row for each index declared for the table, views or table valued function(TVF)
Lists out all schema-scoped object that is created within a database. sys.objects does not show DDL triggers, because they are not schema-scoped. It’s similar to sysobjects system table
Lists out all object that is a procedure of some kind, with sys.objects.type = P, X, RF, and PC
Returns information about tables declared, i.e contains a row for each table object, currently only with sys.objects.type = U. It’s similar to sp_table stored procedure
Contains a row for each view object, with sys.objects.type = V
Contains a row for each object that is a trigger, with a type of TR or TA. DML trigger names are schema-scoped and, therefore, are visible in sys.objects. DDL trigger names are scoped by the parent entity and are only visible in this view
Linked Server Catalog Views
Returns a row per linked-server-login mapping, for use by RPC and distributed queries from local server to the corresponding linked server. This view is similar to sp_helplinkedsrvlogin procedure
Returns a row per remote-login mapping. This catalog view is used to map incoming local logins that claim to be coming from a corresponding server to an actual local login
Contains a row per linked or remote server registered, and a row for the local server that has server_id = 0
Data Spaces and Full-Text Catalog
Contains a row for each data space that is a filegroup
Contains a row for each full-text catalog.
Contains a row for each database schema. Database schemas are different from XML schemas, which are used to define the content model of XML documents.
Security Catalog Views
Returns a row for every permission or column-exception permission in the database. For columns, there is a row for every permission that is different from the corresponding object-level permission. If the column-permission is the same as the corresponding object-permission, there will be no row for it and the actual permission used will be that of the object.
Returns a row for each principal in a database.
Endpoints Catalog Views
Contains one row per endpoint that is created in the system. There is always exactly one SYSTEM endpoint.
Contains one row for the database mirroring endpoint of the server instance.