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 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.
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.
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:
Some other functions that can be used to return system data are DB_ID, DB_NAME, OBJECT_NAME, FILE_NAME, and GETDATE.