Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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

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.

Comments

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.

cheers

Martin.

Leave a Comment

Please register or log in to leave a comment.