• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1309
  • Last Modified:

information schema view

What is the meaning of information_scehma.tables view

when i create database db1 , later i created a schema called schema1

then i created a table called tbl1 in the schema schema1

what does querying information_schema table view mean  does it just display the table schema?
3 Solutions
information_schema exposes information from  the system tables where SQL Server keeps track of things.  The example you cite, will return information about tables.   Generally speaking, the information_schema chases down many of the foreign keys and brings back textual information that may be actually stored in many different system tables.  The theory is that using the information_schema insulates you from future changes to the internals of how/where various objects are stored.

If you do "select * from information_schema.tables", then you will see a row with basic information about the table you just created.  You can get the same information by querying the system tables yourself, but it's not considered a good practice.  

Note that microsoft also supplies dynamic management views, which is a very similar concept.  I think information_schema is implemented at a higher level and complies with some standard body.

It displays the table catalog (qualifier), table name, table type and table schema. Here is some detailed information:

INFORMATION_SCHEMA.TABLES view provides information from System tables. Thus it make sure that you get the same information even when there is change in system tables in future version

Querying INFORMATION_SCHEMA.TABLES provides the following information,

DB1                         Sprockets             NineProngs     BASE TABLE

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now