How to use ObjectProperty to locate problems in tables

There are plenty of very useful object properties that can help us to find problems in our databases. We can use object properties to find tables without primary key, tables without clustered index and much more information.

Object properties simplifies our queries, without them we would need to do much more complex queries over DMV’s to find the same information.

For example, to find if the table has clustered index, primary key, timestamp and identity, we can use the following query:

We can filter the information to get only tables that has some problems, like tables without primary keys or without clustered index:

We can create even a better solution with a table valued function that we can re-use:

We can use these queries together a DDL trigger to disallow the creation of tables without primary key. Let’s see how this trigger will work:

You can see a list with all the properties at https://msdn.microsoft.com/pt-br/library/ms176105.aspx