Other Methods of Querying the System Tables

So far, this chapter has focused on stored procedures to retrieve information from the database and system catalog. Two other methods, information schema views and system functions, also can be used to retrieve metadata.

Information Schema Views

Information schema views were defined by ANSI-92 as a set of views to provide system data. By using views, the actual system tables are hidden from the application. Changes made to the system tables don't affect the application because the application doesn't directly address the system tables. In this way, an application can retrieve data from an ANSI-92 information_schema “compliant system independent of the database vendor or version.

ANSI-92 and SQL Server support a three-part naming schema when referencing objects on the local server. The ANSI-92 terminology refers to catalog.schema.object, whereas the Microsoft equivalent is database.owner.object. If the database is not specified, it defaults to the current database. If the owner is not specified, it defaults to the current login ID. This is important in the context of information schema views because they are "owned" by INFORMATION_SCHEMA. Therefore, when referencing an information schema view in the current database, you must supply the owner.object portion of the naming schema. For example, to find out table information, the command would be as follows :

 SELECT * FROM INFORMATION_SCHEMA.TABLES

Fortunately, the names of the information schema views are fairly intuitive. Some of the information schema views available are TABLES, COLUMNS, TABLE_PRIVILEGES, and VIEWS.

NOTE

Avoid writing queries that reference the system tables directly. Microsoft tries to provide backward compatibility whenever possible, but it is not guaranteed . What this means is that Microsoft reserves the right to change the names of the system tables at any time. If you have applications that call these tables, they might not migrate to the next release. The information schema views, on the other hand, will remain consistent.

System and Metadata Functions

Another way to query system information from within Transact-SQL statements is to use system or metadata functions. These are scalar functions, meaning they return single specific values. For example, to retrieve the username for the current session, use the following:

 SELECT SUSER_NAME()

Some other functions that can be used to return system data are DB_ID , DB_NAME , OBJECT_NAME , FILE_NAME , and GETDATE .