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.

What Are the INFORMATION_SCHEMA Views?
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

database

Catalog

owner

Schema

object

Object

user-defined data type

Domain

The TABLES View
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

Description

TABLE_CATALOG

nvarchar(128)

Table qualifier

TABLE_SCHEMA

nvarchar(128)

Table owner

TABLE_NAME

sysname

Table name

TABLE_TYPE

varchar(10)

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.

SYSOBJECTS versus TABLES View
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:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a