Export (0) Print
Expand All
1 out of 1 rated this helpful - Rate this topic

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.

Topic link icon Transact-SQL Syntax Conventions

OBJECTPROPERTYEX ( id , property )
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 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 may not hold for all rows.

1 = True

0 = False

Base data type: int

CnstIsUpdateCascade

Constraint

FOREIGN KEY constraint with the ON UPDATE CASCADE option.

1 = True

0 = False

Base data type: int

ExecIsAfterTrigger

Trigger

AFTER trigger.

1 = True

0 = False

Base data type: int

ExecIsAnsiNullsOn

Transact-SQL function, Transact-SQL procedure, Transact-SQL trigger, view

The setting of ANSI_NULLS at creation time.

1 = True

0 = False

Base data type: int

ExecIsDeleteTrigger

Trigger

DELETE trigger.

1 = True

0 = False

Base data type: int

ExecIsFirstDeleteTrigger

Trigger

The first trigger fired when a DELETE is executed against the table.

1 = True

0 = False

Base data type: int

ExecIsFirstInsertTrigger

Trigger

The first trigger fired when an INSERT is executed against the table.

1 = True

0 = False

Base data type: int

ExecIsFirstUpdateTrigger

Trigger

The first trigger fired when an UPDATE is executed against the table.

1 = True

0 = False

Base data type: int

ExecIsInsertTrigger

Trigger

INSERT trigger.

1 = True

0 = False

Base data type: int

ExecIsInsteadOfTrigger

Trigger

INSTEAD OF trigger.

1 = True

0 = False

Base data type: int

ExecIsLastDeleteTrigger

Trigger

Last trigger fired when a DELETE is executed against the table.

1 = True

0 = False

Base data type: int

ExecIsLastInsertTrigger

Trigger

Last trigger fired when an INSERT is executed against the table.

1 = True

0 = False

Base data type: int

ExecIsLastUpdateTrigger

Trigger

Last trigger fired when an UPDATE is executed against the table.

1 = True

0 = False

Base data type: int

ExecIsQuotedIdentOn

Transact-SQL function, Transact-SQL procedure, Transact-SQL trigger, view

Setting of QUOTED_IDENTIFIER at creation time.

1 = True

0 = False

Base data type: int

ExecIsStartup

Procedure

Startup procedure.

1 = True

0 = False

Base data type: int

ExecIsTriggerDisabled

Trigger

Disabled trigger.

1 = True

0 = False

Base data type: int

ExecIsTriggerNotForRepl

Trigger

Trigger defined as NOT FOR REPLICATION.

1 = True

0 = False

Base data type: int

ExecIsUpdateTrigger

Trigger

UPDATE trigger.

1 = True

0 = False

Base data type: int

HasAfterTrigger

Table, view

Table or view has an AFTER trigger.

1 = True

0 = False

Base data type: int

HasDeleteTrigger

Table, view

Table or view has a DELETE trigger.

1 = True

0 = False

Base data type: int

HasInsertTrigger

Table, view

Table or view has an INSERT trigger.

1 = True

0 = False

Base data type: int

HasInsteadOfTrigger

Table, view

Table or view has an INSTEAD OF trigger.

1 = True

0 = False

Base data type: int

HasUpdateTrigger

Table, view

Table or view has an UPDATE trigger.

1 = True

0 = False

Base data type: int

IsAnsiNullsOn

Transact-SQL function, Transact-SQL procedure, table, Transact-SQL trigger, view

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

IsEncrypted

Transact-SQL function, Transact-SQL procedure, table, Transact-SQL trigger, view

Indicates that the original text of the module statement was converted to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server 2005. Users without access to system tables or database files cannot retrieve the obfuscated text. However, the text is available to users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the original procedure from memory at run time.

1 = Encrypted

0 = Not encrypted

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.

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.

1 = Precise

0 = Imprecise

Base data type: int

IsPrimaryKey

Any schema-scoped object

PRIMARY KEY constraint.

1 = True

0 = False

Base data type: int

IsProcedure

Any schema-scoped object

Procedure.

1 = True

0 = False

Base data type: int

IsQuotedIdentOn

CHECK constraint, DEFAULT definition, Transact-SQL function, Transact-SQL procedure, table, Transact-SQL trigger, view

Specifies that the quoted identifier setting for the object is ON, meaning double quotation marks delimit identifiers in all expressions involved in the object definition.

1 = True

0 = False

Base data type: int

IsQueue

Any schema-scoped object

Service Broker Queue

1 = True

0 = False

Base data type: int

IsReplProc

Any schema-scoped object

Replication procedure.

1 = True

0 = False

Base data type: int

IsRule

Any schema-scoped object

Bound rule.

1 = True

0 = False

Base data type: int

IsScalarFunction

Function

Scalar-valued function.

1 = Scalar-valued function

0 = Not scalar-valued function

Base data type: int

IsSchemaBound

Function, view

A schema bound function or view created by using SCHEMABINDING.

1 = Schema-bound

0 = Not schema-bound

Base data type: int

IsSystemTable

Table

System table.

1 = True

0 = False

Base data type: int

IsSystemVerified

Computed column, function, user-defined type, view

The precision and determinism properties of the object can be verified by SQL Server.

1 = True

0 = False

Base data type: int

IsTable

Table

Table.

1 = True

0 = False

Base data type: int

IsTableFunction

Function

Table-valued function.

1 = Table-valued function

0 = Not table-valued function

Base data type: int

IsTrigger

Any schema-scoped object

Trigger.

1 = True

0 = False

Base data type: int

IsUniqueCnst

Any schema-scoped object

UNIQUE constraint.

1 = True

0 = False

Base data type: int

IsUserTable

Table

User-defined table.

1 = True

0 = False

Base data type: int

IsView

View

View.

1 = True

0 = False

Base data type: int

OwnerId

Any schema-scoped object

Owner of the object.

Note Note

The schema owner is not necessarily the object owner. For example, child objects (those where parent_object_id is nonnull) will always return the same owner ID as the parent.

Nonnull = Database user ID of the object owner.

NULL = Unsupported object type, or object ID is not valid.

Base data type: int

SchemaId

Any schema-scoped object

The ID of the schema associated with the object.

Nonnull = Schema ID of the object.

Base data type: int

SystemDataAccess

Function, view

Object accesses system data, system catalogs or virtual system tables, in the local instance of SQL Server.

0 = None

1 = Read

Base data type: int

TableDeleteTrigger

Table

Table has a DELETE trigger.

>1 = ID of first trigger with the specified type.

Base data type: int

TableDeleteTriggerCount

Table

The table has the specified number of DELETE triggers.

Nonnull = Number of DELETE triggers

Base data type: int

TableFullTextMergeStatus

Table

Whether a table that has a full-text index that is currently in merging.

0 = Table does not have a full-text index, or the full-text index is not in merging.

1 = The full-text index is in merging.

TableFullTextBackgroundUpdateIndexOn

Table

The table has full-text background update index (autochange tracking) enabled.

1 = TRUE

0 = FALSE

Base data type: int

TableFulltextCatalogId

Table

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 by insert or update operations since the start of full, incremental, or manual change tracking population.

  • The number of rows processed by insert or update operations 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

Note   This property does not monitor or count deleted rows.

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 part of the definition of a full-text index and semantic index.

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.

5 = Full-text indexing is throttled or paused.

Base data type: int

TableFullTextSemanticExtraction

Table

Table is enabled for semantic indexing.

1 = True

0 = False

Base data type: int

TableHasActiveFulltextIndex

Table

Table has an active full-text index.

1 = True

0 = False

Base data type: int

TableHasCheckCnst

Table

Table has a CHECK constraint.

1 = True

0 = False

Base data type: int

TableHasClustIndex

Table

Table has a clustered index.

1 = True

0 = False

Base data type: int

TableHasDefaultCnst

Table

Table has a DEFAULT constraint.

1 = True

0 = False

Base data type: int

TableHasDeleteTrigger

Table

Table has a DELETE trigger.

1 = True

0 = False

Base data type: int

TableHasForeignKey

Table

Table has a FOREIGN KEY constraint.

1 = True

0 = False

Base data type: int

TableHasForeignRef

Table

Table is referenced by a FOREIGN KEY constraint.

1 = True

0 = False

Base data type: int

TableHasIdentity

Table

Table has an identity column.

1 = True

0 = False

Base data type: int

TableHasIndex

Table

Table has an index of any type.

1 = True

0 = False

Base data type: int

TableHasInsertTrigger

Table

Object has an INSERT trigger.

1 = True

0 = False

Base data type: int

TableHasNonclustIndex

Table

The table has a nonclustered index.

1 = True

0 = False

Base data type: int

TableHasPrimaryKey

Table

Table has a primary key.

1 = True

0 = False

Base data type: int

TableHasRowGuidCol

Table

Table has a ROWGUIDCOL for a uniqueidentifier column.

1 = True

0 = False

Base data type: int

TableHasTextImage

Table

Table has a text, ntext, or image column.

1 = True

0 = False

Base data type: int

TableHasTimestamp

Table

Table has a timestamp column.

1 = True

0 = False

Base data type: int

TableHasUniqueCnst

Table

Table has a UNIQUE constraint.

1 = True

0 = False

Base data type: int

TableHasUpdateTrigger

Table

The object has an UPDATE trigger.

1 = True

0 = False

Base data type: int

TableHasVarDecimalStorageFormat

Table

Table is enabled for vardecimal storage format.

1 = True

0 = False

TableInsertTrigger

Table

Table has an INSERT trigger.

>1 = ID of first trigger with the specified type.

Base data type: int

TableInsertTriggerCount

Table

The table has the specified number of INSERT triggers.

>0 = The number of INSERT triggers.

Base data type: int

TableIsFake

Table

Table is not real. It is materialized internally on demand by the Database Engine.

1 = True

0 = False

Base data type: int

TableIsLockedOnBulkLoad

Table

Table is locked because a bcp or BULK INSERT job.

1 = True

0 = False

Base data type: int

TableIsPinned

Table

Table is pinned to be held in the data cache.

0 = False

This feature is not supported in SQL Server 2005 and later versions.

TableTextInRowLimit

Table

Table has text in row option set.

> 0 = Maximum bytes allowed for text in row.

0 = text in row option is not set.

Base data type: int

TableUpdateTrigger

Table

Table has an UPDATE trigger.

> 1 = ID of first trigger with the specified type.

Base data type: int

TableUpdateTriggerCount

Table

Table has the specified number of UPDATE triggers.

> 0 = The number of UPDATE triggers.

Base data type: int

UserDataAccess

Function, View

Indicates the object accesses user data, user tables, in the local instance of SQL Server.

1 = Read

0 = None

Base data type: int

TableHasColumnSet

Table

Table has a column set.

0 = False

1 = True

For more information, see Use Column Sets.

Cardinality

Table (system or user-defined), view, or index

The number of rows in the specified object.

sql_variant

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

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.

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'AdventureWorks2012.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.

A. Finding the base type of an object

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

USE AdventureWorks2012;
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 AdventureWorks2012;
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 AdventureWorks2012;
GO
SELECT name, object_id, schema_id, type_desc
FROM sys.objects 
WHERE OBJECTPROPERTYEX(object_id, N'TableHasForeignKey') = 1
ORDER BY name;
GO
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.