Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

INFORMATION_SCHEMA: A Map to Your Database

SQL Server 7 provides another method for obtaining metadata: the INFORMATION_SCHEMA views. The article introduces these views and shows how they can provide critical information about your database.


advertisement
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_totals go create table my_table_totals ( table_name varchar(50), totalrecs int null ) go declare cr_table_names cursor for select table_name from INFORMATION_SCHEMA.tables where TABLE_TYPE = 'BASE TABLE' declare @table varchar(50) ,@line varchar(500) open cr_table_names FETCH NEXT FROM cr_table_names into @table WHILE @@FETCH_STATUS = 0 BEGIN 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 @table END CLOSE cr_table_names DEALLOCATE 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.



   
Joe Lax has spent more than 10 years working in various database environments and has been a practicing DBA on all versions of SQL Server from version 4.2 onward. He also is a MCSE and an MCT who recently has started to learn Oracle, which affords him no end of fun. He loves feedback and can be reached here.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap