Edit

Share via

System information schema views (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

An information schema view is one of several methods SQL Server provides for obtaining metadata. Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly, although significant changes were made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.

Important

Some changes were made to the information schema views that break backward compatibility. These changes are described in the articles for the specific views.

SQL Server supports a three-part naming convention when you refer to the current server. The ISO standard also supports a three-part naming convention. However, the names used in both naming conventions are different. The information schema views are defined in a special schema named INFORMATION_SCHEMA. This schema is contained in each database. Each information schema view contains metadata for all data objects stored in that particular database. The following table shows the relationships between the SQL Server names and the SQL standard names.

SQL Server name Maps to this equivalent SQL standard name
Database Catalog
Schema Schema
Object Object
User-defined data type Domain

This name-mapping convention applies to the following SQL Server ISO-compatible views.

Also, some views contain references to different classes of data such as character data or binary data.

When you reference the information schema views, you must use a qualified name that includes the INFORMATION_SCHEMA schema name. For example:

USE AdventureWorks2022;
GO

SELECT TABLE_CATALOG,
       TABLE_SCHEMA,
       TABLE_NAME,
       COLUMN_NAME,
       COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Product';

Permissions

The visibility of the metadata in information schema views is limited to securables that a user either owns or on which the user is granted some permission. For more information, see Metadata Visibility Configuration.

Information schema views are defined server-wide and therefore can't be denied within the context of a user database. To REVOKE or DENY access (SELECT), the master database must be used. By default the public role has SELECT-permission to all information schema views but the content is limited with metadata visibility rules.

You can't deny access to information schema views in Azure SQL Database.

Related content