Getting the most out of SQL Server information schema views

SQL Server information schema views let you access metadata about database objects such tables and columns. Learn how you can access the views to retrieve relevant information.

Information schema views are one of several methods available in SQL Server for accessing metadata. The views let

you retrieve metadata about data 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

Dig deeper on Microsoft SQL Server 2008

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close