INFORMATION_SCHEMA: A Map to Your Database

INFORMATION_SCHEMA: A Map to Your Database

f you’ve spent any time at all working with databases, you know that the time comes when you scratch your head and ask, “Now what was the name of that table (or some other object)?” Programming SQL Server effectively has always involved knowing how to find the metadata?that is, the information about the structure of your database. In previous versions of SQL Server, you could either use Enterprise Manager, some of the catalog stored procedures (such as sp_help) or even queried the system tables directly. (See my previous 10-Minute Solution, “Using Metadata to Solve Complex Problems,” for an example of using the sysobjects table.)

Now in version 7, SQL Server provides another method for obtaining metadata: the INFORMATION_SCHEMA views. In this article, I introduce these views and show you how they can provide critical information about your database.

The INFORMATION_SCHEMA is part of the SQL-92 standard, so you will be able to use your knowledge of these views in other database engines in the future. In SQL Server, these views exist in each database. However, they are not under the “dbo” user. Instead they are under the ownership of INFORMATION_SCHEMA. Therefore, to access any of the views, you need to preface the view name with “INFORMATION_SCHEMA”?that is, select * from INFORMATION_SCHEMA.TABLES. Each of the views in this collection exposes a different aspect of the metadata of the database. In all, there are 17 views.

Translation, Please
When looking at these views, you will find fields with names that don’t readily translate into terms that we currently use in SQL Server. Fortunately, the Books Online has created a translation table for us (see Table 1).

Table 1. Equivalent SQL Server and SQL-92 Names

SQL Server Name

SQL-92 Name







user-defined data type


Let’s take a closer look at the TABLES view. Table 2 shows the structure of this view.

Table 2. Structure of TABLES View

Column name

Data type




Table qualifier



Table owner



Table name



Type of table; can be VIEW or BASE TABLE

Try selecting from this view. The TABLE_CATALOG contains the name of the database. For most objects, the TABLE_SCHEMA contains “dbo.” The INFORMATION_SCHEMA views themselves also appear in this view. However, the TABLE_SCHEMA value for them is INFORMATION_SCHEMA.

Although the name of this view is TABLES, views are also listed. They can be distinguished from tables by the last field, TABLE_TYPE.

Of course, you can still obtain a list of all tables in the database by executing the following: select * from sysobjects where type = ‘U’. However, there are some major differences between the TABLES view and sysobjects:

  • The TABLES view only displays tables on which the user has permissions. It is a useful way of allowing users to see only what they are supposed to know about.
  • The TABLES view is part of a standard. The sysobjects table is not guaranteed to stay the same.

However, this does not mean that you will never use the sysobjects table anymore. In fact, my previous select from sysobjects points out a crucial difference. By using the “where type = ‘U’,” I eliminate all system tables from the list. There is no way to differentiate between system tables and user tables in the TABLES view. (However, in the TABLES view, system tables such as sysobjects are listed with a TABLE_TYPE of view. Perhaps at some point system tables will be under a separate owner rather than dbo.)

The TABLES View in Action
Here is a simple example of how the TABLES view can be used to do much more than just list all the tables. When you look at a database for the first time, it is frequently useful to know how many rows there are in each table. The following code uses the TABLES view to build a cursor of each table name. For each table, it then does a select count(*):

if exists(select * from sysobjects where    type = 'U' and name = 'my_table_totals')   drop table my_table_totalsgocreate table my_table_totals(   table_name varchar(50),   totalrecs int null)godeclare  cr_table_names cursorforselect table_namefrom INFORMATION_SCHEMA.tableswhere TABLE_TYPE = 'BASE TABLE' declare @table varchar(50) ,@line varchar(500)open cr_table_namesFETCH NEXT FROM cr_table_names into @tableWHILE @@FETCH_STATUS = 0BEGIN   select @line ="insert into my_table_totals (table_name, totalrecs)    (" + "select '" + @table + "'" + "," + "(select count(*)    from " + @table + "))"      select @line   exec (@line)    FETCH NEXT FROM cr_table_names into @tableENDCLOSE cr_table_namesDEALLOCATE cr_table_names

In my next article, I’ll take a look at some of the more complicated views that are part of this collection.

Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes