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.
.gif "Topic link icon")
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
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
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.
Return Types
sql_variant
Exceptions
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.
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'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.
Examples
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
See Also
Reference
Metadata Functions (Transact-SQL)
OBJECT_DEFINITION (Transact-SQL)