SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Remember OBJECTPROPERTY(..) when investigating objects.

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


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.


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

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


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.



Posted by cfradenburg on 26 January 2011

The first thing I thought of when looking at this was performance.  Both of my assumptions, that OBJECTPROPERTY was slower than using sys.tables and that using the INFORMATION_SCHEMA objects would be slower than Microsoft's proprietary ones, where proven wrong with some quick testing.  Which leaves the fact that I'm more familiar with the views than OBJECTPROPERTY as the only reason to not change but that can be fixed with a little work.  Thanks for the post.

Posted by Martin Catherall on 26 January 2011

Hi cfradenburg,

Thanks for testing the performance out and leaving a comment - it's really good to know.

I must admit that I had not really considered the performance of this function.



Leave a Comment

Please register or log in to leave a comment.