This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.
.gif)
SQL Essentials: Foreign Key Metadata: Improving on INFORMATION_SCHEMA Views
Ron Talmage
Whether you use INFORMATION_SCHEMA views or not (the ANSI-compliant metadata feature was introduced with SQL7), I think you'll appreciate Ron Talmage's approach to improving on them.
Sometimes you need to perform tasks on a SQL Server 2000 database that require you to access the database's metadata. You can use the system tables to obtain metadata, but Microsoft recommends using the INFORMATION_SCHEMA views and the property functions—its "official" API to a database's metadata. I suspect most DBAs simply access the system tables directly to obtain any metadata they need, but there are occasions, such as when you want to list foreign key (FK) metadata, when using the INFORMATION_SCHEMA is actually easier.
Not too long ago, I needed to create scripts to drop and create FKs, so I wrote a query, coincidentally, based on an article I wrote a couple of years ago (see "Foreign Key Metadata" in the June 2001 issue) to accomplish the task. [You might also want to re-read Ron's June 1999 column on using INFORMATION_SCHEMA views to compare table structures.—Ed.] However, the original query was limited to single-column FKs, and because I really wanted to understand more about the how and why behind INFORMATION_SCHEMA, I dug a little deeper. This month, I'll share my discoveries, including how to create a "missing" INFORMATION_SCHEMA view.
Foreign keys and INFORMATION_SCHEMA views
Foreign key relationships exist between a referencing table that has the FK constraint and the referenced table that has a primary key or uniqueness constraint. The FK relationship can contain keys with more than one column, but the total number of columns on each end of the relationship must match, and so must the data types of the corresponding columns. In other words, you can have a FK with two columns referencing a primary key or uniqueness constraint on another table—as long as the referenced table's key also has two columns, and provided the data types match up. The actual column names don't need to match, as long as you satisfy the other conditions.
SQL Server provides a set of system views in the Master database that you can use in any database to discover metadata. They're called the INFORMATION_SCHEMA views (I'll use the abbreviation "I_S" from now on), and they're based on an ANSI standard method of retrieving metadata about relational tables in a database. You execute an I_S view in a particular database, and just as certain system stored procedures do, the views return data about your current database. When you execute the view, you have to specify the "owner" or schema. For example, the following query gives you information about tables and their columns:
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
When you turn to the I_S views for information about FKs, the most appropriate view is REFERENTIAL_CONSTRAINTS. However, this view is a little disappointing because it's incomplete: It contains information relating FK names to primary or uniqueness constraint names, but it makes no mention of the tables in the relationship, and, significantly, nothing about the columns involved in the keys. Without that information, you don't know enough to document or reverse engineer the FKs.
There are several other I_S views that might provide more supporting information to fill out what REFERENTIAL_CONSTRAINTS is missing. Information about all table constraints is contained in TABLE_CONSTRAINTS, while information about constraints and columns is contained in two I_S views, CONSTRAINT_COLUMN_USAGE and KEY_COLUMN_USAGE.
Reverse engineering the design
The main question here is, how do these views really work? Just selecting from them can be rather confusing; what we really need to know is how the views relate to each other. Even though the views aren't tables (views are "virtual" tables), there are implicit relationships between them, and we need to know those relationships in order to join the views properly. To surface those implicit relationships, I exported the data from all four I_S views just mentioned into a set of tables and then created FK relationships. You can see the result in Figure 1, which I obtained using Enterprise Manager's Database Diagram tool. (In order to create the primary keys on the resulting tables, I shortened the length of all the system names from nvarchar(128) to nvarchar(50), since you can't have more than 900 bytes total in a primary key.)
.jpg){kind=link}
Having an entity-relationship (ER) diagram of the I_S views makes it a lot easier to determine how one might "extend" them. In this diagram, I've drawn implicit relationships from all the constraint tables back to TABLE_CONSTRAINTS. (TABLE_CONSTRAINTS, KEY_COLUMN_USAGE, and CONSTRAINT_COLUMN_USAGE also have an implicit relationship back to TABLES, but I didn't include that information in order to keep the diagram focused on the FK information.)
Note first of all that the REFERENTIAL_CONSTRAINTS view is a join based on TABLE_CONSTRAINTS. It lists one row for each FK constraint, and matches the latter with the primary key or uniqueness constraint that the FK references. It also includes the MATCH, ON UPDATE, and ON DELETE FK options. It's missing the tables for each side of the relationship, but that information is included in TABLE_CONSTRAINTS. Also missing, as I mentioned earlier, is the list of columns involved in each key.
To get missing information about the columns, you must turn to KEY_COLUMN_USAGE or CONSTRAINT_COLUMN_USAGE. (Note that CONSTRAINT_COLUMN_USAGE shows column names but not their order.) KEY_COLUMN_USAGE is almost identical, but contains the essential missing piece, the ORDINAL_POSITION, which states the column order for each constraint. Because of this, the KEY_COLUMN_USAGE view is much more useful than the CONSTRAINT_COLUMN_USAGE view (which looks superfluous). If your FKs contain only single-column primary keys, then CONSTRAINT_COLUMN_USAGE will work, and that's what I used in my column of two years ago. However, if your database contains compound primary keys, you'll need KEY_COLUMN_USAGE. So for a completely general solution, we must use KEY_COLUMN_USAGE.
Creating the missing view
You've seen that you must somehow join these views in order to get a full report on FKs. What we need is a query that will return the FK constraint name, the FK table, and each FK column in the table, along with the column order. We need to see that information matched to the referenced primary key constraint, and table, and column. In fact, if this query were a view, we could get the essential FK column metadata much more easily. Let's develop the query and then create the view. We'll start by creating a simple query that will get the FK table, column, and column order:
SELECT TABLE_NAME
, CONSTRAINT_NAME
, COLUMN_NAME
, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME IN (
SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS)
ORDER BY TABLE_NAME, ORDINAL_POSITION
This query filters the data in the KEY_COLUMN_USAGE view by restricting the constraints in question to those that are listed in the REFERENTIAL_CONSTRAINTS view. Because it uses an IN clause, it cannot pick up any further information from the REFERENTIAL_CONSTRAINTS view, such as the referenced constraint name.
To get additional information from REFERENTIAL_CONSTRAINTS, you need a JOIN rather than an IN, as shown here:
SELECT
KCU.CONSTRAINT_NAME AS 'FK_CONSTRAINT_NAME'
, KCU.TABLE_NAME AS 'FK_TABLE_NAME'
, KCU.COLUMN_NAME AS 'FK_COLUMN_NAME'
, KCU.ORDINAL_POSITION AS 'FK_ORDINAL_POSITION'
, RC.UNIQUE_CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
ON KCU.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
ORDER BY KCU.CONSTRAINT_NAME, KCU.ORDINAL_POSITION
You might be tempted to start with KEY_COLUMN_USAGE and then join with REFERENTIAL_CONSTRAINTS. The result would be the same, but if you start with REFERENTIAL_CONSTRAINTS, you'll soon see that there's a significant benefit.
At this point, you could just clone the preceding query to get information about primary and uniqueness constraints, and then join the two as derived tables in order to get matched data. However, a simpler way is to add another join to KEY_COLUMN_USAGE. You can make a separate reference to it, alias it separately, and then add the referenced key information, as in the following query:
SELECT
KCU1.CONSTRAINT_NAME AS 'FK_CONSTRAINT_NAME'
, KCU1.TABLE_NAME AS 'FK_TABLE_NAME'
, KCU1.COLUMN_NAME AS 'FK_COLUMN_NAME'
, KCU1.ORDINAL_POSITION AS 'FK_ORDINAL_POSITION'
, KCU2.CONSTRAINT_NAME AS 'UQ_CONSTRAINT_NAME'
, KCU2.TABLE_NAME AS 'UQ_TABLE_NAME'
, KCU2.COLUMN_NAME AS 'UQ_COLUMN_NAME'
, KCU2.ORDINAL_POSITION AS 'UQ_ORDINAL_POSITION'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
ON KCU2.CONSTRAINT_CATALOG =
RC.UNIQUE_CONSTRAINT_CATALOG
AND KCU2.CONSTRAINT_SCHEMA =
RC.UNIQUE_CONSTRAINT_SCHEMA
AND KCU2.CONSTRAINT_NAME =
RC.UNIQUE_CONSTRAINT_NAME
AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
This query takes the initial information for the FK constraint from KEY_COLUMN_USAGE and joins it with the primary key or uniqueness constraint on the referenced table. Since the number of columns must be the same between the FK and the referenced key, and their order must be the same, the query can join the second reference to KEY_COLUMN_USAGE on the ORDINAL_POSITION, as you can see in the last row. Now you have all the important information about a FK constraint that was missing from the initial REFERENTIAL_CONSTRAINTS view. By the way, in the preceding query, the 'FK_CONSTRAINT_NAME' style of naming columns explicitly distinguishes the FK constraint information from that of the referenced (primary key or uniqueness constraint) table.
A new INFORMATION_SCHEMA view
It would be nice to have the previous query as a view—in fact, as an INFORMATION_SCHEMA type of view. We have the KEY_COLUMN_USAGE view and REFERENTIAL_CONSTRAINTS view, but what we really need is a REFERENTIAL_CONSTRAINTS_COLUMN_USAGE view, one that would show at a glance the columns used by FK constraints. The following view definition will do the job:
USE master
GO
IF OBJECT_ID('INFORMATION_SCHEMA.
REFERENTIAL_CONSTRAINTS_COLUMN_USAGE') IS NOT NULL
DROP VIEW INFORMATION_SCHEMA.
REFERENTIAL_CONSTRAINTS_COLUMN_USAGE
GO
CREATE VIEW INFORMATION_SCHEMA.
REFERENTIAL_CONSTRAINTS_COLUMN_USAGE
AS
SELECT
KCU1.CONSTRAINT_CATALOG AS 'CONSTRAINT_CATALOG'
, KCU1.CONSTRAINT_SCHEMA AS 'CONSTRAINT_SCHEMA'
, KCU1.CONSTRAINT_NAME AS 'CONSTRAINT_NAME'
, KCU1.TABLE_CATALOG AS 'TABLE_CATALOG'
, KCU1.TABLE_SCHEMA AS 'TABLE_SCHEMA'
, KCU1.TABLE_NAME AS 'TABLE_NAME'
, KCU1.COLUMN_NAME AS 'COLUMN_NAME'
, KCU1.ORDINAL_POSITION AS 'ORDINAL_POSITION'
, KCU2.CONSTRAINT_CATALOG
AS 'UNIQUE_CONSTRAINT_CATALOG'
, KCU2.CONSTRAINT_SCHEMA
AS 'UNIQUE_CONSTRAINT_SCHEMA'
, KCU2.CONSTRAINT_NAME AS 'UNIQUE_CONSTRAINT_NAME'
, KCU2.TABLE_CATALOG AS 'UNIQUE_TABLE_CATALOG'
, KCU2.TABLE_SCHEMA AS 'UNIQUE_TABLE_SCHEMA'
, KCU2.TABLE_NAME AS 'UNIQUE_TABLE_NAME'
, KCU2.COLUMN_NAME AS 'UNIQUE_COLUMN_NAME'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU1.CONSTRAINT_SCHEMA =
RC.CONSTRAINT_SCHEMA
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
ON KCU2.CONSTRAINT_CATALOG =
RC.UNIQUE_CONSTRAINT_CATALOG
AND KCU2.CONSTRAINT_SCHEMA =
RC.UNIQUE_CONSTRAINT_SCHEMA
AND KCU2.CONSTRAINT_NAME =
RC.UNIQUE_CONSTRAINT_NAME
WHERE KCU1.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
GO
In this view, the column names match the REFERENTIAL_CONSTRAINTS, and it also includes the catalog (database) and schema (owner)—following the style of the I_S views.
Once you create the new view in the master database, all you need to do to see the columns involved in a FK constraint is execute the following:
SELECT *
FROM REFERENTIAL_CONSTRAINTS_COLUMN_USAGE
ORDER BY CONSTRAINT_NAME, ORDINAL_POSITION
Creating new system objects in the master database is always a risky venture, but in this case, we've just created a view that will only be used for utility purposes. If you want your database to be completely self-contained, you can create the view there instead of in master. Next month, you'll learn how to use this view to document and reverse engineer both create and drop scripts for FKs. Enjoy!
Download RON1203.SQL
To find out more about Microsoft SQL Server Professional and Pinnacle Publishing, visit their Web site at http://www.pinpub.com/
Note: This is not a Microsoft Corporation Web site. Microsoft is not responsible for its content.
This article is reproduced from the December 2003 issue of Microsoft SQL Server Professional. Copyright 2001, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Microsoft SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.