The object property function appears to be often overlooked.

This function will tell us about any schema scoped object in the current database.

For me, it is far easier to use the OBJECTPROPERTY() function, than to write a complicated query that might join serveral catalog views.

The function takes two parameters

OBJECTPROPERTY (id,property name)

The first is an integer that represents the ID of the object - I always use the OBJECT_ID function here to extract the correct ID.

The second is the "property name" - as listed in the function documentation.

As an example, suppose we have a customer table in the current database and we want to know if there is a clustered index on that table.

Well, we have a few options here,

• We could go look in SSMS - but that's a manual step and is no good if we want to do this in a script.
• Write a query on sys.indexes and sys.columns.
• simple use the  OBJECTPROPERTY() function to ask if a clustered index exists on the table.

In this case we'd use the function like so

SELECT

OBJECTPROPERTY(OBJECT_ID('dbo.Customers') , 'TableHasClustIndex');

A beter example may be to list all tables from your database that do not have a clustered index.

select * from INFORMATION_SCHEMA.TABLES

where OBJECTPROPERTY(OBJECT_ID('' + TABLE_SCHEMA + '.' + TABLE_NAME +'') , 'TableHasClustIndex') = 0;

Or, Select all tables in the database with no primary key

select * from INFORMATION_SCHEMA.TABLES

where OBJECTPROPERTY(OBJECT_ID('' + TABLE_SCHEMA + '.' + TABLE_NAME +'') , 'TableHasPrimaryKey') = 0;

If you like this function then you might like to check out its close cousin SERVERPROPERTY

Have fun.

Martin.