INFORMATION_SCHEMA Views


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).

image from book
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.




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net