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.

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

Object type

Description and values returned

BaseType

Any schema-scoped object

Identifies the base type of the object. When the specified object is a SYNONYM, the base type of the underlying object is returned.

Nonnull = Object type

Base data type: char(2)

CnstIsClustKey

Constraint

PRIMARY KEY constraint with a clustered index.

1 = True

0 = False

Base data type: int

CnstIsColumn

Constraint

CHECK, DEFAULT, or FOREIGN KEY constraint on a single column.

1 = True

0 = False

Base data type: int

CnstIsDeleteCascade

Constraint

FOREIGN KEY constraint with the ON DELETE CASCADE option.

1 = True

0 = False

Base data type: int

CnstIsDisabled

Constraint

Disabled constraint.

1 = True

0 = False

Base data type: int

CnstIsNonclustKey

Constraint

PRIMARY KEY constraint with a nonclustered index.

1 = True

0 = False

Base data type: int

CnstIsNotRepl

Constraint

Constraint is defined by using the NOT FOR REPLICATION keywords.

1 = True

0 = False

Base data type: int

CnstIsNotTrusted

Constraint

Constraint was enabled without checking existing rows. Therefore, the constraint might not hold for all rows.

Specifies that the ANSI NULLS option setting for the table is ON, meaning all comparisons against a null value evaluate to UNKNOWN. This setting applies to all expressions in the table definition, including computed columns and constraints, for as long as the table exists.

1 = True

0 = False

Base data type: int

IsCheckCnst

Any schema-scoped object

CHECK constraint.

1 = True

0 = False

Base data type: int

IsConstraint

Any schema-scoped object

Constraint.

1 = True

0 = False

Base data type: int

IsDefault

Any schema-scoped object

Bound default.

1 = True

0 = False

Base data type: int

IsDefaultCnst

Any schema-scoped object

DEFAULT constraint.

1 = True

0 = False

Base data type: int

IsDeterministic

Scalar and table-valued functions, view

The determinism property of the function or view.

1 = Deterministic

0 = Not Deterministic

Base data type: int

IsExecuted

Any schema-scoped object

Specifies the object can be executed (view, procedure, function, or trigger).

1 = True

0 = False

Base data type: int

IsExtendedProc

Any schema-scoped object

Extended procedure.

1 = True

0 = False

Base data type: int

IsForeignKey

Any schema-scoped object

FOREIGN KEY constraint.

1 = True

0 = False

Base data type: int

IsIndexed

Table, view

A table or view with an index.

1 = True

0 = False

Base data type: int

IsIndexable

Table, view

A table or view on which an index may be created.

1 = True

0 = False

Base data type: int

IsInlineFunction

Function

Inline function.

1 = Inline function

0 = Not inline function

Base data type: int

IsMSShipped

Any schema-scoped object

An object created during installation of SQL Server 2005.

1 = True

0 = False

Base data type: int

IsPrecise

Computed column, function, user-defined type, view

Indicates whether the object contains an imprecise computation, such as floating point operations.

ID of the full-text catalog in which the full-text index data for the table resides.

Nonzero = Full-text catalog ID, associated with the unique index that identifies the rows in a full-text indexed table.

0 = Table does not have a full-text index.

Base data type: int

TableFullTextChangeTrackingOn

Table

Table has full-text change-tracking enabled.

1 = TRUE

0 = FALSE

Base data type: int

TableFulltextDocsProcessed

Table

Number of rows processed since the start of full-text indexing. In a table that is being indexed for full-text search, all the columns of one row are considered as part of one document to be indexed.

0 = No active crawl or full-text indexing is completed.

> 0 = One of the following:

The number of documents processed since the start of Full, Incremental, or Manual change tracking population.

The number of rows processed since change tracking with background update index population was enabled, the full-text index schema changed, the full-text catalog rebuilt, or the instance of SQL Server restarted, and so on.

NULL = Table does not have a full-text index.

Base data type: int

TableFulltextFailCount

Table

The number of rows that full-text search did not index.

0 = The population has completed.

>0 = One of the following:

The number of documents that were not indexed since the start of Full, Incremental, and Manual Update change tracking population.

For change tracking with background update index, the number of rows that were not indexed since the start of the population, or the restart of the population. This could be caused by a schema change, rebuild of the catalog, server restart, and so on

NULL = Table does not have a Full-Text index.

Base data type: int

TableFulltextItemCount

Table

Nonnull = Number of rows that were full-text indexed successfully.

NULL = Table does not have a full-text index.

Base data type: int

TableFulltextKeyColumn

Table

ID of the column associated with the single-column unique index that is participating in the full-text index definition.

0 = Table does not have a full-text index.

Base data type: int

TableFulltextPendingChanges

Table

Number of pending change tracking entries to process.

0 = change tracking is not enabled.

NULL = Table does not have a full-text index.

Base data type: int

TableFulltextPopulateStatus

Table

0 = Idle.

1 = Full population is in progress.

2 = Incremental population is in progress.

3 = Propagation of tracked changes is in progress.

4 = Background update index is in progress, such as autochange tracking.

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.

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.

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.