Information Schema Views

Introduction

It's always been my nature to find out how and why things work.  I know

when I was young I drove my parents crazy with all my questions.  Now my

dad smiles that smile of sweet revenge since my four year-old has inherited that

aspect of me.  My wife just glares at me when our little boy starts on his

stream of questions.  Naturally, as a DBA, I'm interested in how SQL Server

works.  As a result, I feel in my element delving into the system

tables.  However, Microsoft reserves the right to change those tables at

any time.  So if I'm writing scripts to hand a developer or someone less

curious about SQL Server, I want to make sure I'm going to hand them something

that's not in danger of breaking when Microsoft rolls out the next version of

SQL Server or even the next service pack.  A quick glance of changes

between SQL Server 6.x and 7.0/2000 show a lot of differences in the system

tables.    

Handing a solution using system tables to another SQL Server DBA is in most

cases just fine.  DBAs tend to be an inquisitive lot and if a script

breaks, finding out what broke and why is part of the fun.  But if I'm

handing off to a developer who's primary focus is VB or VC++ or even Java, it is

doubtful that the developer has the time to investigate SQL Server's system

tables, especially since there's always something to be learned in the

developer's primary language(s).  If the query I've written is part of some

reporting solution for a manager, I cannot expect the manager to delve into SQL

Server's internals to figure out how to fix it when it breaks, since most

managers don't have enough hours in the day as it is.  Thankfully, for most

of the information we may need about the database, there are the Information

Schema views.  Microsoft implements Information Schema views for compliance

with the SQL-92 standard:

In each catalog

in an SQL-environment, there is a schema, the Information Schema, with the name

INFORMATION_SCHEMA, containing a number of view descriptors, one base table

descriptor, and several domain descriptors. The data accessible through these

views is a representation of all of the descriptors in all of the schemas in

that catalog.

Information Schema is mandated by SQL-92 in order to provide metadata about a

given database (catalog).  Since it's there and it's part of a standard, it

represents a possible choice for reporting about a database if the solution is

intended for non-DBAs.  Our solutions should be sound, but they also need

to be tailored for use based on our audience.  On that note, let's take a

little journey into the world of Information Schema views.

The List

Let's start our review of Information Schema views by using one of them,

INFORMATION_SCHEMA.Views.  There are a couple of columns we're interested

in: TABLE_SCHEMA and TABLE_NAME. TABLE_SCHEMA corresponds to the view owner, and

in this case we're looking at all views where the owner is

INFORMATION_SCHEMA.  The TABLE_NAME is self-explanatory, the name of the

view itself.  The following query:

SELECT

TABLE_NAME
FROM INFORMATION_SCHEMA.Views
WHERE TABLE_SCHEMA =

'INFORMATION_SCHEMA'
ORDER BY TABLE_NAME

returns:

There are 20 Information Schema views for SQL Server 2000.  SQL Server

7.0 has quite a few as well, 17 all told.  Since there are more than a

handful, let's group them based on the information they report.  In a

couple of cases, the particular Information Schema view could belong in multiple

categories, but for simplicity's sake, we'll just put each of them in only one

category.

* Information Schema views for Stored Procedures and User-Defined

Functions are not found in SQL Server 7.0

Points to Remember

Naming Conventions

Information Schema views are based on the SQL-92 standard, and as a result,

the column names used do not necessarily match the column names we are

accustomed to with SQL Server.  However, the names aren't so foreign that

they are unusable.  Here's how they match up:

If the names seem a little unwieldy, we can simply alias them to what we're

more accustomed:

SELECT

TABLE_CATALOG [Database], TABLE_SCHEMA [Owner], TABLE_NAME [Table], TABLE_Type

[Type] 
FROM INFORMATION_SCHEMA.Tables

Otherwise the field names are straightforward and what we are used to

seeing. 

Permissions

If we build queries against the system tables we give a user access to those

queries either by granting select permissions against the system tables or by

putting the queries into stored procedures.  From there, even if a user

doesn't have permissions against a particular object, so long as the user has

permissions to the system tables, the user is able to retrieve information about

the object.  This isn't the case with Information Schema views.  They

are built such that a user only sees information about the objects he or she has

access to use.  If we list out the view definition for

INFORMATION_SCHEMA.Tables (run the sp_helptext from the master database

if using SQL Server 2000, which is what I did here), here's what we see:

--Identifies

tables accessible to the current user 
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

The view definition makes use of the PERMISSIONS() function, which

will evaluate only those objects which the current user can access.  This

has both a good side and a bad side.  If we're looking to allow a user to

only get information on the objects he or she has access to, Information Schema

views are ideal.  The checking is already done in the view

definition.  If, however, we want the user to see information on all

objects, we've got to either build our own solution or grant the user rights to

all the objects (such as through the use of the db_datareader database

role).

With all that said, if we don't want to go through the hassle of building

queries against the system tables, the Information Schema views represent an

ideal place to turn.  Most of the work has been done for us.  And

since we'd be accessing the database with db_owner rights, we have

permissions on every object.  As a result, with the possible exception of

the views concerning privileges, all the metadata on the database will be at our

fingertips based on this permission model.

A Few Gaps

Looking at the list of 20 Information Schema views, we do notice a few

gaps.  There aren't any views reporting on indexing, nor are there any

reporting on users and groups.  In SQL Server 7.0, stored procedures are

also not covered.  Remember, the Information Schema views come from the

SQL-92 standard and these are areas which aren't covered.  As a result, if

we want information on indexes or on users/groups/roles, we still do need to

either go to system stored procedures such as sp_helpindex or directly to

the system tables to gather the information we need. And in SQL Server 7.0,

we'll need to go to sp_stored_procedures and the standby

sp_helptext.

Concluding Remarks

Information Schema views are great to use for reporting the metadata about a

particular database.  Instituted in compliance with SQL-92, Information

Schema views represent a fairly stable set of views to use for this purpose, as

opposed to the system tables, which Microsoft reserves the right to alter

without notice.  There are quite a few of them, 17 in 7.0 and 20 in 2000,

but there are a few gaps.  We won't find information about indexing nor any

on users and groups.  Also, the Information Schema views only report on

objects which the user has permissions to access.  As a result, if we're

fairly restrictive on our permissions, Information Schema views is probably not

the best choice. However, with all that said, they are sometimes the ideal focus

for our queries reporting on our databases.