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.
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
user-defined data type
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
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.