Information schema views are one of several methods available in SQL Server for accessing metadata. The views let you retrieve metadata about data
Requires Free Membership to View
objects stored within the database. For example, you can use SQL Server information
schema views to access details about the data types, nullability or default values of the columns
in a table.
First supported in SQL Server 2005, information schema views are automatically added to a database in a schema named INFORMATION_SCHEMA, created specifically for the views. They retrieve some of the same type of information as catalog views but are not as extensive. However, information schema views are compliant with protocols laid out by the International Organization for Standardization, or ISO. That means the naming conventions used by the views are consistent with SQL standards. If your application code targets different versions of SQL Server or other database management systems, use information schema views.
The following table shows the primary ISO-compliant object names used by the views and how those names correspond to object names in SQL Server.
| SQL standard object name | SQL Server object name |
| catalog | database |
| schema | schema |
| object | object |
| domain | user-defined data type |
You can use SQL Server information schema views to access metadata about such database objects as tables, columns, views, parameters, privileges, constraints and other object types. To access this information, reference the view as you would any user-defined view or table. For instance, in the following SELECT statement, I retrieve column-specific information through the COLUMNS information schema view.
USE AdventureWorks2008;
GO
SELECT
COLUMN_NAME,
DATA_TYPE,
IS_NULLABLE,
COLUMN_DEFAULT
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'Employee';
Notice that I first change the current database to AdventureWorks2008. (I use this sample database -- on a local instance of SQL Server 2008 -- for all the examples in this article.) Then I specify the SELECT statement that calls the view. In this case, I’m calling the view in the FROM clause, as I would call a table or any other kind of view. Because SQL Server information schema views are defined within INFORMATION_SCHEMA, you must qualify the view name with the schema name, as I’ve done in “INFORMATION_SCHEMA.COLUMNS.”
My example also specifies the names of several of the view’s columns in the SELECT list. You can retrieve the name and description of each column supported by a view by referring to the topic specific to that view in SQL Server Books Online. I’ve also limited the results returned by the view by including a WHERE clause that specifies that the TABLE_NAME value must equal Employee. The following results show the Employee-specific data returned by the SELECT statement.
| COLUMN_NAME | DATA_TYPE | IS_NULLABLE | COLUMN_DEFAULT |
| BusinessEntityID | int | NO | NULL |
| NationalIDNumber | nvarchar | NO | NULL |
| LoginID | nvarchar | NO | NULL |
| OrganizationNode | hierarchyid | NO | NULL |
| OrganizationLevel | smallint | NO | NULL |
| JobTitle | nvarchar | NO | NULL |
| BirthDate | date | NO | NULL |
| MaritalStatus | nchar | NO | NULL |
| Gender | nchar | NO | NULL |
| HireDate | date | NO | NULL |
| SalariedFlag | bit | NO | ((1)) |
| VacationHours | smallint | NO | ((0)) |
| SickLeaveHours | smallint | NO | ((0)) |
| CurrentFlag | bit | NO | ((1)) |
| rowguid | uniqueidentifier | NO | (newid()) |
| ModifiedDate | datetime | NO | (getdate()) |
You can just as easily retrieve metadata through any of the other information schema views. For example, in the following SELECT statement, I use the TABLES view to retrieve the name and type of each table in the HumanResources schema.
USE AdventureWorks2008;
GO
SELECT
TABLE_NAME,
TABLE_TYPE
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = 'HumanResources';
Again, I make certain that AdventureWorks2008 is the current database. In the SELECT statement, I specify the TABLE_NAME and TABLE_TYPE column in the SELECT list, specify the TABLES information schema view in the FROM clause (qualified with the INFORMATION_SCHEMA schema name), and then limit my search in the WHERE clause. As the following results indicate, the HumanResources schema includes both base tables and views.
| TABLE_NAME | TABLE_TYPE |
| Department | BASE TABLE |
| Employee | BASE TABLE |
| EmployeeDepartmentHistory | BASE TABLE |
| EmployeePayHistory | BASE TABLE |
| JobCandidate | BASE TABLE |
| vEmployee | VIEW |
| vEmployeeDepartment | VIEW |
| vEmployeeDepartmentHistory | VIEW |
| vJobCandidate | VIEW |
| vJobCandidateEmployment | VIEW |
| vJobCandidateEducation | VIEW |
| Shift | BASE TABLE |
That’s all there is to using information schema views. You can find a list of the views supported by SQL Server by referring to the topic “Information Schema Views (Transact-SQL)” in SQL Server Books Online. From here, you can link to the topic associated with each view. Besides describing the columns returned by the views, the topics provide additional information that is important to understanding the view. For the most part, however, SQL Server information schema views are easy to understand and use. And because they’re created automatically when you create a database, you can start using them immediately.
ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books,
articles and training material related to Microsoft Windows, various relational database management
systems, and business intelligence design and implementation. Find more information at http://rhsheldon.com.
This was first published in May 2011
Join the conversationComment
Share
Comments
Results
Contribute to the conversation