| Home |
| Articles |
| Backup |
| Books |
| Certification |
| FAQ |
| Products |
| Replication |
| Scripts |
| Seminars |
| Training |
| TSQL |
|
| MSDN Fourms |
| Philippine SSUG |
| Fort Worth SSUG |
| Oklahoma City SSDG |
|
| Resume |
|
| MHS Enterprises |
| BlowFrog Software |
| FilAm Software |
| AcrylicAcetate.com |
| Bargain Humidors |
| Western Humidor |
|
|
Find Foreign Keys 1 2 3 4 5
Foreign keys and declarative referential
integrity (DRI) can be both a blessing and a curse. The blessings are that you get
RI that is enforced no matter what you do. This is also a curse as those who have
tried to selectively BCP into tables that use DRI have found out. Using DRI is also
supposedly fatser than using triggers to support your RI. While this makes sense,
because DRI is compiled directly into the table definition whereas triggers are compiled
as separate objects. But triggers offer one thing that is not available for
DRI. That is a nice error message from SQL Server that can be displayed to a
user. You might say this is not important, but how many times have users called up
because some really nasty looking message that is actually rather harmless has appeared on
their screen. If you are using DRI, being able to find and test the RI via another
means before actually inserting can be a lifesaver. Also, getting a list of the
primary key to foreign key relationships is handy when trying to modify the system.
So, I have written a script to do just that. (This is actually a reproduction of
something I did for one of our clients with about 2/3 of the script stripped out and a few
things added in, so yes this is used in real life.)Download the script here: find_fkeys.sql
The table we are going to be working with is sysreferences, sysconstraints, sysobjects,
and syscolumns. Sysreferences like almost every other table in the system catalog
does a perfect job of violating every relational design principle. (This isn't a
fault of Microsoft as the system tables are still pretty much intact from the Sybase
days.) This must have been a big joke at both Sybase and Microsoft where they design
a relational database and help develop some of the relational design principles and then
promptly violated every single one to develop the design that their entire relational
structure sits upon!
Enough of my ramblings, you are out here for the script and the explanation of what is
in it, what it does, and how that really nasty looking SQL statement actually works.
As a background, below are the structures of the tables we will be working with.
| CREATE TABLE dbo.syscolumns |
CREATE TABLE dbo.sysobjects |
| (id int NOT NULL, |
(name sysname NOT NULL, |
| number smallint NOT NULL, |
id int NOT NULL, |
| colid tinyint NOT NULL, |
uid smallint NOT NULL, |
| status tinyint NOT NULL, |
type char(2) NOT NULL, |
| type tinyint NOT NULL, |
userstat smallint NOT NULL, |
| length tinyint NOT NULL, |
sysstat smallint NOT NULL, |
| offset smallint NOT NULL, |
indexdel smallint NOT NULL, |
| usertype smallint NOT NULL, |
schema_ver smallint NOT NULL, |
| cdefault int NOT NULL, |
refdate datetime NOT NULL, |
| domain int NOT NULL, |
crdate datetime NOT NULL, |
| name sysname NOT NULL, |
version int NOT NULL, |
| printfmt varchar(255) NULL, |
deltrig int NOT NULL, |
| prec tinyint NULL, |
instrig int NOT NULL, |
| scale tinyint NULL) |
updtrig int NOT NULL, |
|
seltrig int NOT NULL, |
|
category int NOT NULL, |
|
cache smallint NOT NULL) |
|
|
| CREATE TABLE dbo.sysreferences |
|
| (constid int NOT NULL, |
|
| fkeyid int NOT NULL, |
CREATE TABLE
dbo.sysconstraints |
| fkeydbid smallint NOT NULL, |
(constid int NOT NULL, |
| rkeyid int NOT NULL, |
id int NOT NULL, |
| rkeydbid smallint NOT NULL, |
colid tinyint NOT NULL, |
| rkeyindid smallint NOT NULL, |
spare1 tinyint NOT NULL, |
| keycnt smallint NOT NULL, |
status int NOT NULL, |
| fkey1 tinyint NOT NULL, |
actions int NOT NULL, |
| fkey2 tinyint NOT NULL, |
error int NOT NULL) |
| fkey3 tinyint NOT NULL, |
|
| fkey4 tinyint NOT NULL, |
|
| fkey5 tinyint NOT NULL, |
|
| fkey6 tinyint NOT NULL, |
|
| fkey7 tinyint NOT NULL, |
|
| fkey8 tinyint NOT NULL, |
|
| fkey9 tinyint NOT NULL, |
|
| fkey10 tinyint NOT NULL, |
|
| fkey11 tinyint NOT NULL, |
|
| fkey12 tinyint NOT NULL, |
|
| fkey13 tinyint NOT NULL, |
|
| fkey14 tinyint NOT NULL, |
|
| fkey15 tinyint NOT NULL, |
|
| fkey16 tinyint NOT NULL, |
|
| rkey1 tinyint NOT NULL, |
|
| rkey2 tinyint NOT NULL, |
|
| rkey3 tinyint NOT NULL, |
|
| rkey4 tinyint NOT NULL, |
|
| rkey5 tinyint NOT NULL, |
|
| rkey6 tinyint NOT NULL, |
|
| rkey7 tinyint NOT NULL, |
|
| rkey8 tinyint NOT NULL, |
|
| rkey9 tinyint NOT NULL, |
|
| rkey10 tinyint NOT NULL, |
|
| rkey11 tinyint NOT NULL, |
|
| rkey12 tinyint NOT NULL, |
|
| rkey13 tinyint NOT NULL, |
|
| rkey14 tinyint NOT NULL, |
|
| rkey15 tinyint NOT NULL, |
|
| rkey16 tinyint NOT NULL) |
|
Find Foreign Keys 1 2 3 4 5
|
