information schema view

Posted on 2007-10-14
Last Modified: 2011-09-20
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?
Question by:salesprod
    LVL 42

    Accepted Solution

    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.

    LVL 13

    Assisted Solution

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

    Assisted Solution

    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

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how the fundamental information of how to create a table.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now