A Primer on INFORMATION_SCHEMA Views in SQL Server 2000

Introduction

The word meta-data has several meanings and definitions. Meta-data describes the structure and meaning of data. In short, it explains the structure (or definitions) of existing data. It can also be defined as “data about data.”

From the Microsoft SQL Server 2000 point of view, meta-data is something about exploring the structures (or definitions) of entire database schema, including each and every object (tables, views, and so forth) present within that schema. Meta-data would include, for example, a list of tables, a list of the parameters of a particular stored procedure, a list of all user-defined functions, and so on.

Microsoft® SQL Server™ 2000 provides two methods for obtaining meta-data: system stored procedures and information schema views. System stored procedures will be generally (or heavily) used by DBAs to get any information about an SQL Server database. But, it would be quite hard to remember all of the internal stored procedures and their hierarchies. Another issue is that the system stored procedures tend to change from every version of SQL Server.  Lots of differences (or enhancements) in system stored procedures exist from SQL Server 6.5 to 7.0 and even to 2000 (and of course to 2005) as well.

The better, the best and the simple way to get this type of information (meta-information about database) is to use INFORMATION_SCHEMA views. These views provide an internal, system table-independent view of the SQL Server meta-data. Information schema views allow applications to work properly, even though significant changes have been made to the system tables. These are most recommended for application programmers when they would like to play with database system information. The information schema views included in SQL Server conform to the SQL-92 Standard definition for the INFORMATION_SCHEMA.

These views are defined in a special schema named INFORMATION_SCHEMA, which is contained in each database. Each INFORMATION_SCHEMA view contains meta-data for all of the data objects stored in that particular database.

High-level overview of all INFORMATION_SCHEMA views

Now, the issue is how to get all the information schema views in the form of a list. The following command helps to rescue us (but make sure to execute the command from the "master" database).

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.Views

WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'

ORDER BY TABLE_NAME

 

The above command lists out all the views you can use with information_schema. Following is a table which shows a brief description of all of the views available from the above command (which has been highly summarized from Books Online for the sake of convenience).

To use any of the above views, just add the respective view to the "information_schema" word separated with a dot as follows.

select * from information_schema.tables 

Most commonly used queries on INFORMATION_SCHEMA views

The following queries may help application programmers to get the most out of INFORMATION_SCHEMA views in a practical way. You can execute these queries on any database of any SQL Server 2000 instance.

The following command gives the list of tables available in the database:

select table_name

from information_schema.tables

where table_type='BASE TABLE'

The following command gives the list of columns of every table in the database, including their data types and widths. You can also use a WHERE clause, if you would like to deal with only one table.

select table_name,column_name, data_type, character_maximum_length as width

from information_schema.columns

order by table_name,ordinal_position

The following command gives the list of all views and their definitions from the database.

select table_name as view_name, view_definition from information_schema.views

The following command gives all the list stored procedures and their definitions (unless they are encrypted) available in the database. To get the list of stored functions just replace the word ‘PROCEDURE’ with ‘FUNCTION’.

select routine_name, routine_definition

from information_schema.routines

where routine_type='PROCEDURE'

The following command gives you all the foreign key constraints (relationships) together with their respective primary keys of parents.

select constraint_name as 'foreign key', unique_constraint_name as 'parent primary key'

from information_schema.referential_constraints

The following gives you all the lists of parameters of every routine (stored procedure or stored function) along with their data types and widths.

select specific_name as [routine name],parameter_name, data_type, character_maximum_length as [width]

from information_schema.parameters

order by specific_name,ordinal_position

The above examples are just to help you understand how this works. You can also design more complicated queries by joining the INFORMATION_SCHEMA views together according to the requirements.

Internals of INFORMATION_SCHEMA views

Now that you  have enough of an understanding of INFORMATION_SCHEMA views, let us move on to further internals of INFORMATION_SCHEMA views. 

What made the INFORMATION_SCHEMA views so powerful? Are they re-written by Microsoft to help the application programmers? Do they having any relation to system stored procedures or functions? Can we create our own views together with INFORMATION_SCHEMA views and some system stored procedures?

Several questions could be extracted from our present knowledge on INFORMATION_SCHEMA. But, primarily there is a fundamental answer for any of the questions above.

INFORMATION_SCHEMA VIEWS ARE JUST THE VIEWS BASED ON EXISTING SYSTEM STORED PROCEDURES OR FUNCTIONS.

I hope the above answers almost all of the questions. Microsoft made our life easier by creating the INFORMATION_SCHEMA views on top of system stored procedures. Now, how do you know the internal query of a certain view present in INFORMATION_SCHEMA? This is a very simple and situational question according to the current topic. Of course, the answer is also simple, too. Let us consider the following.

I would like to know how Microsoft SQL Server team implemented INFORMATION_SCHEMA.TABLES view. Just type the following in your query analyzer (generally using ‘master’ database), and you should be able to see the result.

execute sp_helptext "information_schema.tables"

The result returned by that would be as following:

create view INFORMATION_SCHEMA.TABLES

as

select distinct

     db_name()              as TABLE_CATALOG

     ,user_name(o.uid)as TABLE_SCHEMA

     ,o.name                      as TABLE_NAME

     ,case o.xtype

           when 'U' then 'BASE TABLE'

           when 'V' then 'VIEW'

     end                          as TABLE_TYPE

from

     sysobjects o

where

     o.xtype in ('U', 'V') and

     permissions(o.id) != 0

I hope you understand the secret behind the INFORMATION_SCHEMA views now. They are nothing but the queries written by the SQL Server team to make our life easier, instead forcing us to remember a complicated query using the system stored procedures and functions.

Summary

From all the above sections of this article, we could conclude something like the following:

• INFORMATION_SCHEMA views are easy to understand and remember. Use INFORMATION_SCHEMA views wherever possible and applicable.
  
  
• Use system stored procedures if and only if you cannot get an alternative from INFORMATION_SCHEMA views.
  
  
• Using system stored procedures heavily in your application may cause versioning problems, when SQL Server gets upgraded to a newer version.
  
  
• You are always safe (at least better than using system stored procedures) using INFORMATION_SCHEMA views, even after an upgrade to a newer version.
  
  
• Try to create your own schema of views, for your own queries, which are very frequently being used on existing INFORMATION_SCHEMA views.

I also advise you to know and learn about the internal system table and system stored procedure hierarchy, to give you a solid understanding of database activities. Even though it is not so easy to remember all of those issues, at least the concept would help us to derive our own powerful, administrational and consolidated queries, which might be used very frequently. Generally, almost any SQL Server DBA would certainly know about almost all of the internal structures and hierarchies needed to make and keep the database stable in performance.

I would certainly be happy to receive your comments (or feedback) on this article at jag_chat@yahoo.com.