OBJECTPROPERTYEX (Transact-SQL)

Returns information about schema-scoped objects in the current database. For a list of these objects, see sys.objects (Transact-SQL). OBJECTPROPERTYEX cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications.

Transact-SQL Syntax Conventions

Syntax

OBJECTPROPERTYEX ( id , property )

Arguments

• id
  Is an expression that represents the ID of the object in the current database. id is int and is assumed to be a schema-scoped object in the current database context.

• property
  Is an expression that contains the information to be returned for the object specified by id. The return type is sql_variant. The following table shows the base data type for each property value.

  Note

  Unless noted otherwise,NULL is returned when property is not a valid property name, id is not a valid object ID, id is an unsupported object type for the specified property, or the caller does not have permission to view the object's metadata.

  Property name

Return Types

sql_variant

Exceptions

Returns NULL on error or if a caller does not have permission to view the object.

In SQL Server 2005, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as OBJECTPROPERTYEX may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration and Troubleshooting Metadata Visibility.

Remarks

The Database Engine assumes that object_id is in the current database context. A query that references an object_id in another database will return NULL or incorrect results. For example, in the following query the current database context is the master database. The Database Engine will try to return the property value for the specified object_id in that database instead of the database that is specified in the query. The query returns incorrect results because the view vEmployee is not in the master database.

USE master;
GO
SELECT OBJECTPROPERTYEX(OBJECT_ID(N'AdventureWorks.HumanResources.vEmployee'), 'IsView');
GO

OBJECTPROPERTYEX(view_id**,'IsIndexable'**) may consume significant computer resources because evaluation of IsIndexable property requires the parsing of view definition, normalization, and partial optimization. Although the IsIndexable property identifies tables or views that can be indexed, the actual creation of the index still might fail if certain index key requirements are not met. For more information, see CREATE INDEX (Transact-SQL).

OBJECTPROPERTYEX (table_id**,'TableHasActiveFulltextIndex'**) will return a value of 1 (true) when at least one column of a table is added for indexing. Full-text indexing becomes active for population as soon as the first column is added for indexing.

Restrictions on metadata visibility are applied to the result set. For more information, see Metadata Visibility Configuration.

Examples

A. Finding the base type of an object

The following example creates a SYNONYM MyEmployeeTable for the Employee table in the AdventureWorks database and then returns the base type of the SYNONYM.

USE AdventureWorks;
GO
CREATE SYNONYM MyEmployeeTable FOR HumanResources.Employee;
GO
SELECT OBJECTPROPERTYEX ( object_id(N'MyEmployeeTable'), N'BaseType')AS [Base Type];
GO

The result set shows that the base type of the underlying object, the Employee table, is a user table.

Base Type 
-------- 
U 

B. Returning a property value

The following example returns the number of UPDATE triggers on the specified table.

USE AdventureWorks;
GO
SELECT OBJECTPROPERTYEX(OBJECT_ID(N'HumanResources.Employee'), N'TABLEUPDATETRIGGERCOUNT');
GO

C. Finding tables that have a FOREIGN KEY constraint

The following example uses the TableHasForeignKey property to return all the tables that have a FOREIGN KEY constraint.

USE AdventureWorks;
GO
SELECT name, object_id, schema_id, type_desc
FROM sys.objects 
WHERE OBJECTPROPERTYEX(object_id, N'TableHasForeignKey') = 1
ORDER BY name;
GO

See Also

Reference

CREATE SYNONYM (Transact-SQL)
Metadata Functions (Transact-SQL)
OBJECT_DEFINITION (Transact-SQL)
OBJECT_ID (Transact-SQL)
OBJECT_NAME (Transact-SQL)
sys.objects (Transact-SQL)
ALTER AUTHORIZATION (Transact-SQL)
TYPEPROPERTY (Transact-SQL)

Other Resources

User-Schema Separation

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
12 December 2006	New content: Added the TableHasVarDecimalStorageFormat property available in Service Pack 2.
17 July 2006	New content: Added the Exceptions section.
5 December 2005	Changed content: Corrected the definition of the IsTableFunction and IsScalarFunction properties. Modified example C.