SQL Server 2005 contains a group of system views that are used to obtain metadata. Their names consist of three parts. The first part is the database name (optional), the second part is always INFORMATION_SCHEMA (as opposed to being the database owner, which is why these views are so named), and the third part references the type of metadata that the view contains.
In Figure 8-2, you can see the usage of an INFORMATION_SCHEMA.TABLES view. It returns the names of all tables (and views—virtual tables) that the current user has permission to see in the current database. INFORMATION_SCHEMA views work like system stored procedures—they are defined in the master database, but they return information in the context of the current database (or the database that is referenced in the first part of the name).
Figure 8-2: Using INFORMATION_SCHEMA views
Note | INFORMATION_SCHEMA views are designed to be compatible witb SQL-92 naming standards. Therefore, instead of database-, owner-, object-, and user-defined data types, INFORMATION_SCHEMA views are named witb catalog, scbema, object, and domain as a tbird part of tbe name, respectively. |
Microsoft recommends that you reference these views (as well as system stored procedures) instead of directly referencing system tables in your procedures.