HawkeSoft SQL Tips and Tricks - Part 6 - The Power of OBJECTPROPERTY

OBJECTPROPERTY is your one-stop shopping function for finding out all kinds of system information on database objects. It is especially powerful for triggers, contraints, and indexes. The sample code below shows one of my favorite uses for this function, finding disabled and untrusted constraints. This can be extremely useful when bad data has been introducted into a system, or to check clean up code after a data migration/conversion. (SQL 2000/2005)

SELECT name, tbl = object_name(parent_obj)
FROM sysobjects
WHERE objectproperty(id, 'CnstIsDisabled') = 1

SELECT name, tbl = object_name(parent_obj)
FROM sysobjects
WHERE objectproperty(id, 'CnstIsNotTrusted') = 1

To read more check out http://msdn.microsoft.com/en-us/library/ms176105.aspx.