SQL Server’s Metadata Views

Introduction:

Every good Database Management System (DBMS) has some sort of
data dictionary or metadata and SQL Server is no exception.  In reality,
SQL has two sources for us to view the metadata: the various system tables and
the INFORMATION_SCHEMA views.  In this article we’ll take a closer look
at the INFORMATION_SCHEMA views, some limitations on their use, and some
possible applications for them.  

But first, we should ask the question, "What’s wrong with
pulling information from sysobjects and other system tables?"  Well,
the standard answer is Microsoft says not to do so.  Microsoft reserves the
right to change any and all system tables from version to version and that
would break any applications that rely upon them.  With that said, when we
choose to let Enterprise Manager auto-generate a T-SQL script for us
about our objects, it uses the system tables.  Eh?  But if we change
versions doesn’t that mean our scripts could then fail?  Most likely
not.  The use of the system tables by the script generator is pretty straight forward and it’s
unlikely that any of the fields used by these scripts would be changed or
dropped.  So basically using the system tables shouldn’t be a real issue;
we just have to remember that Microsoft has pre-qualified their warning. 
With that said, we can avoid many of the potential problems due to version
change by going to the INFORMATION_SCHEMA views.  There are some other reasons
why we might choose to us them, so let’s do some exploring, shall we?

What They Show:

INFORMATION_SCHEMA views do a lot of the work required to
decipher and link up the system tables for us.  For instance, if we want
a simple list of tables we have access to, we can simply enter:

  SELECT * FROM
INFORMATION_SCHEMA.TABLES

Here’s an excerpt of what we’ll see from the Northwind database:

 

TABLE_CATALOG

TABLE_SCHEMA TABLE_NAME TABLE_TYPE
Northwind dbo Alphabetical list of products VIEW
Northwind dbo Categories BASE TABLE
Northwind dbo Category Sales for 1997 VIEW
Northwind dbo Current Product List VIEW

The table_catalog field is just going to tell us what
database we’re in.  The table_schema field, however, tells us the
owner, and of course, the table_type tells us whether or not we’ve got
a regular table or a view on our hands.  INFORMATION_SCHEMA.Tables is pretty straight
forward in the information it reports back.

Most of the rest of
the views are just as simple.  All told, there
are 17 different metadata views in SQL Server 7 and 20 in SQL Server 2000.
They cover the gamut from tables and columns to constraints to domains
(user-defined data types) to permissions on the various database
objects.

Two of the things we’ve got to remember about
INFORMATION_SCHEMA views are the issues of permissions and ownership.  If
we do a query against the system tables (provided we have permission), we can
return a list of all the tables or columns or constraints that exist. 
This isn’t the case with the INFORMATION_SCHEMA views. 
INFORMATION_SCHEMA views in some cases return what objects are accessible by
the user (as is the case with INFORMATION_SCHEMA.Tables), but in other cases,
it’ll only return information on the objects the querying user owns (as with
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS).  Now, if we’re talking about
reporting on permissions themselves, the views will report the permissions
either a) granted to the current user or b) granted by the current user. 
These permissions and ownership factors carry into any stored procedures we may use to query the
views as well.

If we think about it, though, we can use the permissions and
ownerships restrictions to our advantage.  Let’s say I have a database
and I’ve got quite a few tables in it.  Let’s say I have several groups
of users, all with different sets of access to these tables.  By
utilizing INFORMATION_SCHEMA.Tables, I can show only the tables that are
accessible by a particular user.  The user doesn’t see a list of all the
tables and I don’t have to any extra work to only retrieve the tables the
particular user should be able to see. Likewise, let’s say I’m developing a
database schema along with some other teammates.  At this point all the
objects are not owned by dbo but by the respective developer. I want to check
on just the constraints I have created.  The appropriate
INFORMATION_SCHEMA views handle this just fine without me having to worry
about building a somewhat complex query. 

Doing Some Reporting:

About a month ago, a developer friend of mine asked the
question, "How do I generate metadata reports of our current database
using Crystal Reports?"  I thought for a moment and said, "Use
INFORMATION_SCHEMA views."  In his case, all of the objects were
owned by dbo, a standard practice.  What he needed to report back on
were the names of tables, the columns they contained, and the basic data types of
those columns.  Management wanted a drill down capability, just the sort
of thing that CR is good at doing.  We can combine the various views
whether using Crystal’s own data manipulation methods or by writing a query
similar to the following:

SELECT T.TABLE_NAME AS [Table], C.COLUMN_NAME AS [Column], 
C.IS_NULLABLE AS [Allows Nulls?], C.DATA_TYPE AS [Type]
FROM INFORMATION_SCHEMA.Tables T JOIN INFORMATION_SCHEMA.Columns C
ON T.TABLE_NAME = C.TABLE_NAME
WHERE T.TABLE_NAME NOT LIKE ‘sys%’
AND T.TABLE_NAME <> ‘dtproperties’
AND T.TABLE_SCHEMA <> ‘INFORMATION_SCHEMA’
ORDER BY T.TABLE_NAME, C.ORDINAL_POSITION

Here I’ve joined the Tables and Columns views and I’ve
intentionally filtered to remove any system tables (NOT LIKE ‘sys%’), the
database diagrams table (dtproperties), and the INFORMATION_SCHEMA views
themselves.  Here’s a sample of what’s returned for the Categories table
in the Northwind database:

 

Table

Column

Allows Nulls?

Type
Categories CategoryID No int
Categories CategoryName No nvarchar
Categories Description YES ntext
Categories Picture YES image

If I need to generate a report in CR or some other tool about
my database structure, the INFORMATION_SCHEMA views usually are all I need as
the source of my information.  Though there should be this documentation
ahead of time, it’s probably in a formal design document.   That
tends to be a harder source to retrieve information out of than these views.

Concluding Remarks:

While INFORMATION_SCHEMA views have some limitations and some
factors to consider, they can be very useful.  I haven’t covered them in
great detail here, this article served more as an introduction, but hopefully
along with some forays into Books Online you’ll find a place for them in your
toolbox.  They are great for producing very quick reports about our
database structure and they can also give us the ability through other apps to
report back to a user exactly what he or she can access without breaking our backs
in the process.  With all that said, I will say that I don’t use them
that often.  However, the times I have, they have proven
invaluable.  Hopefully you’ll find them as useful.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles