Information Schema Views (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms186778.aspx

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.

CHECK_CONSTRAINTS REFERENTIAL_CONSTRAINTS
COLUMN_DOMAIN_USAGE ROUTINES
COLUMN_PRIVILEGES ROUTINE_COLUMNS
COLUMNS SCHEMATA
CONSTRAINT_COLUMN_USAGE TABLE_CONSTRAINTS
CONSTRAINT_TABLE_USAGE TABLE_PRIVILEGES
DOMAIN_CONSTRAINTS TABLES
DOMAINS VIEW_COLUMN_USAGE
KEY_COLUMN_USAGE VIEW_TABLE_USAGE
PARAMETERS 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:

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

GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s