http://www.sqlservercentral.com/blogs/martin_catherall/2011/01/20/remember-objectproperty-when-investigating-objects/

Printed 2014/07/22 12:01PM

Remember OBJECTPROPERTY(..) when investigating objects.

By Martin Catherall, 2011/01/20

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,

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.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.