declare
@table_name varchar(50),
@column_name varchar(50),
@constraint_name varchar(50),
@int int,
@Mint int
declare @pk_tbls table
(idn numeric(18) identity(1,1),
table_name varchar(30),
column_name varchar(30),
constraint_name varchar(30))
declare @missing_fk_cols table
(idn numeric(18) identity(1,1),
table_name varchar(30),
column_name varchar(30))
insert into @pk_tbls(table_name,column_name,constraint_name)
select table_name,column_name,CONSTRAINT_NAME from information_schema.constraint_column_usage
where CONSTRAINT_NAME like '%pk'
select @Mint = max(idn) from @pk_tbls
set @int = 1
while @mint >= @int
begin
select @table_name = table_name, @column_name = column_name,@constraint_name = constraint_name from @pk_tbls where idn = @int
insert into @missing_fk_cols(table_name,column_name)
select TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where column_name = @column_name
and table_name not like 'v_%'
and table_name not in
(select substring(CONSTRAINT_NAME,1,(len(CONSTRAINT_NAME)-4)) tblname from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
where UNIQUE_CONSTRAINT_NAME = @constraint_name)
and table_name <> substring(@constraint_name,1,(len(@constraint_name)-3))
set @int = @int+1
end
select * from @pk_tbls
select * from @missing_fk_cols
