Detection of Broken Forign Key Relationship
Under special circumstances, it could happen that database contains "orphan" records. Data in dependant table exists without it's "parent" record in a master table. It could be caused by a bulk insert operation without CHECK_CONSTRAINTS option, or by disabling foreign key for a while or adding a foreign key to a table that already contains data without checking existing data for validity.
In SQL2000, there is a function DBCC CHECKCONSTRAINTS, that checks all tables (or just a chosen one) for consistency with all (or chosen) constraints. (actually, there was a bug in version 8.00.194 that didn't allow to run this statement against database with special character eg. space in a table name eg. Northwind database :-)).
As far as I know there is no similar functionality in MS SQL7.
If you would like to check your data, you can try following SP. It has one optional parameter for a name of a table. If you don't specify the name, all the user tables in a current database will be checked. The output format is similar to DBCC CHECKCONSTRAINTS.
If you need to check just broken FOREIGN KEY relationship, enclosed stored procedure runs faster than DBCC.
CREATE PROCEDURE dbo.Check_FK
@tbl_name sysname = 'all'
AS
declare @sql_str as varchar(2000) --this string contains all long select statement
declare @fk_idx int --index of constraint within sysrefernces table
set @fk_idx=-1
--rather than cursor, use while statement.
while exists (select constid from dbo.sysreferences where constid>@fk_idx)
begin
set @fk_idx=(
select top 1 constid from dbo.sysreferences
where constid>@fk_idx and (Object_name(fkeyid)=@tbl_name or @tbl_name='all')
order by constid)
set @sql_str=(select '
if exists(
select 1 from ['+Object_name(rkeyid)+'] a right join ['+ Object_name(fkeyid) + '] b on a.['+
ISnull( col_name(rkeyid,rkey1)+'] = b.['+col_name(fkeyid,fkey1)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey2)+'] = b.['+col_name(fkeyid,fkey2)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey3)+'] = b.['+col_name(fkeyid,fkey3)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey4)+'] = b.['+col_name(fkeyid,fkey4)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey5)+'] = b.['+col_name(fkeyid,fkey5)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey6)+'] = b.['+col_name(fkeyid,fkey6)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey7)+'] = b.['+col_name(fkeyid,fkey7)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey8)+'] = b.['+col_name(fkeyid,fkey8)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey9)+'] = b.['+col_name(fkeyid,fkey9)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey10)+'] = b.['+col_name(fkeyid,fkey10)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey11)+'] = b.['+col_name(fkeyid,fkey11)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey12)+'] = b.['+col_name(fkeyid,fkey12)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey13)+'] = b.['+col_name(fkeyid,fkey13)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey14)+'] = b.['+col_name(fkeyid,fkey14)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey15)+'] = b.['+col_name(fkeyid,fkey15)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey16)+'] = b.['+col_name(fkeyid,fkey16)+']','')+
' where a.'+col_name(rkeyid,rkey1)+' is null '+
Isnull(' and b.'+col_name(fkeyid,fkey1)+' is not null','')+
Isnull(' and b.'+col_name(fkeyid,fkey2)+' is not null','')+
Isnull(' and b.'+col_name(fkeyid,fkey3)+' is not null','')+
Isnull(' and b.'+col_name(fkeyid,fkey4)+' is not null','')+
Isnull(' and b.'+col_name(fkeyid,fkey5)+' is not null','')+
Isnull(' and b.'+col_name(fkeyid,fkey6)+' is not null','')+
Isnull(' and b.'+col_name(fkeyid,fkey7)+' is not null','')+
Isnull(' and b.'+col_name(fkeyid,fkey8)+' is not null','')+
Isnull(' and b.'+col_name(fkeyid,fkey9)+' is not null','')+
Isnull(' and b.'+col_name(fkeyid,fkey10)+' is not null','')+
Isnull(' and b.'+col_name(fkeyid,fkey11)+' is not null','')+
Isnull(' and b.'+col_name(fkeyid,fkey12)+' is not null','')+
Isnull(' and b.'+col_name(fkeyid,fkey13)+' is not null','')+
Isnull(' and b.'+col_name(fkeyid,fkey14)+' is not null','')+
Isnull(' and b.'+col_name(fkeyid,fkey15)+' is not null','')+
Isnull(' and b.'+col_name(fkeyid,fkey16)+' is not null','')
+
')
select '''+Object_Name(constid)+''' as FK_name,
'''+Object_Name(rkeyid)+''' as Referenced_tbl,
'''+Object_Name(fkeyid)+''' as Referencing_tbl,
b.* from ['+Object_name(rkeyid)+'] a right join ['+ Object_name(fkeyid) + '] b on a.['+
ISnull( col_name(rkeyid,rkey1)+'] = b.['+col_name(fkeyid,fkey1)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey2)+'] = b.['+col_name(fkeyid,fkey2)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey3)+'] = b.['+col_name(fkeyid,fkey3)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey4)+'] = b.['+col_name(fkeyid,fkey4)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey5)+'] = b.['+col_name(fkeyid,fkey5)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey6)+'] = b.['+col_name(fkeyid,fkey6)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey7)+'] = b.['+col_name(fkeyid,fkey7)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey8)+'] = b.['+col_name(fkeyid,fkey8)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey9)+'] = b.['+col_name(fkeyid,fkey9)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey10)+'] = b.['+col_name(fkeyid,fkey10)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey11)+'] = b.['+col_name(fkeyid,fkey11)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey12)+'] = b.['+col_name(fkeyid,fkey12)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey13)+'] = b.['+col_name(fkeyid,fkey13)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey14)+'] = b.['+col_name(fkeyid,fkey14)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey15)+'] = b.['+col_name(fkeyid,fkey15)+']','')+
isnull(' and a.['+ col_name(rkeyid,rkey16)+'] = b.['+col_name(fkeyid,fkey16)+']','')+
' where a.['+col_name(rkeyid,rkey1)+'] is null '+
Isnull(' and b.['+col_name(fkeyid,fkey1)+'] is not null','')+
Isnull(' and b.['+col_name(fkeyid,fkey2)+'] is not null','')+
Isnull(' and b.['+col_name(fkeyid,fkey3)+'] is not null','')+
Isnull(' and b.['+col_name(fkeyid,fkey4)+'] is not null','')+
Isnull(' and b.['+col_name(fkeyid,fkey5)+'] is not null','')+
Isnull(' and b.['+col_name(fkeyid,fkey6)+'] is not null','')+
Isnull(' and b.['+col_name(fkeyid,fkey7)+'] is not null','')+
Isnull(' and b.['+col_name(fkeyid,fkey8)+'] is not null','')+
Isnull(' and b.['+col_name(fkeyid,fkey9)+'] is not null','')+
Isnull(' and b.['+col_name(fkeyid,fkey10)+'] is not null','')+
Isnull(' and b.['+col_name(fkeyid,fkey11)+'] is not null','')+
Isnull(' and b.['+col_name(fkeyid,fkey12)+'] is not null','')+
Isnull(' and b.['+col_name(fkeyid,fkey13)+'] is not null','')+
Isnull(' and b.['+col_name(fkeyid,fkey14)+'] is not null','')+
Isnull(' and b.['+col_name(fkeyid,fkey15)+'] is not null','')+
Isnull(' and b.['+col_name(fkeyid,fkey16)+'] is not null','')
from dbo.sysreferences where constid=@fk_idx)
exec (@sql_str)
end
GO 
