OBJECTPROPERTY Function In SQL Server

SQL Server has an excellent metadata function OBJECTPROPERTY() that allows to view a lot of properties of database objects. For example, if table has a trigger, clustered key, check constraint or default. The full list is available on MSDN article OBJECTPROPERTY (Transact-SQL).

For example, if you want to list all tables that has no primary key, you can do this with a simple query: