Friday, July 16, 2010

Query system catalogs for foreign key constraints

putting together my latest warehouse (on a very tight deadline) I noticed that
some ETL jobs were taking way longer than expected. I thought I had disabled all
referential constraints, but while digging through (there were quite a few of
them - and that little FK window is darn near useless) I found one that was
still enforcing RI. Rather than dig through all the tables, why not just hit the
system catalogs? Ran across this nifty little script... this will show you all
constraints sorted by enabled / disabled.

select [status] =
          when objectproperty(constid, 'CnstIsDisabled') =then 'Enabled'
          else 'Disabled'
      [constraint_name] = object_name(constid),
      [table_name] = object_name(fKeyId),
      [column_name] = col_name(fKeyId, fKey),
      [referenced_table_name] = object_name(rKeyId),
      [referenced_column_name] = col_name(rKeyId, rKey)
from sysforeignkeys
 objectproperty(constid, 'CnstIsDisabled') = 0
order by 1

No comments:

Post a Comment