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.