Frequently Used Catalog Views – SQL 2005

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

sys.configurations

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.

sys.traces

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

sys.databases

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

sys.master_files

This catalog view will provide enough information about database’s physical file settings. This view is similar to sysaltfiles system table in SQL2000.

sys.database_files

The sys.database_files view will provide information about the working database. This is similar to sp_helpdb stored procedure.

Object Catalog Views

sys.columns

List out all the columns declared for tables and views.

sys.indexes

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)

sys.objects

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

sys.procedures

Lists out all object that is a procedure of some kind, with sys.objects.type = P, X, RF, and PC

sys.tables

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

sys.views

Contains a row for each view object, with sys.objects.type = V

sys.triggers

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

sys.linked_logins

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

sys.remote_logins

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

sys.servers

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

sys.filegroups

Contains a row for each data space that is a filegroup

sys.fulltext_catalogs

Contains a row for each full-text catalog.

Schemas

sys.schemas

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

sys.database_permissions

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.

sys.database_principals

Returns a row for each principal in a database.

Endpoints Catalog Views
sys.endpoints

Contains one row per endpoint that is created in the system. There is always exactly one SYSTEM endpoint.

sys.database_mirroring_endpoints

Contains one row for the database mirroring endpoint of the server instance.