Friday, March 23, 2007

SQL Server 2005 Access to System tables

In SQL2K5 system data is hidden in resource tables and cannot be accessed. Instead, there are number of views which can provide the information.

Catalog Views – Shows metadata that describes the objects in an instance
Information Schema Views - They have the same functionality as in 2000 Provides internal view of SQL Server meta data
Compatibility Views – These are for backward compatibility. Beware that columns that previously were undocumented typically now only have NULL or 0
Dynamic Management Views – They provide inside details of SQL Server state data, which can be used to check the health of SQL Server instance. Consists of both views and table valued functions

Also from BOL, the catalog views and the compatibility views have restricted visibility, so by default they only return information about objects to which the user has some rights.

Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views

No comments: