OBJECTPROPERTYEX#
Returns information about schema-scoped objects in the current database. OBJECTPROPERTYEX cannot be used for objects that are not schema-scoped.
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.
Property name |
Object type |
Description and values returned |
|---|---|---|
BaseType |
Any schema-scoped object |
Not supported |
SPARK_PARTITIONING |
A Spark based DB schema-scoped object |
If available returns partitioning configuration for an object. String content or empty string. |
ExecIsAnsiNullsOn |
Transact-SQL function, Transact-SQL procedure, Transact-SQL trigger, view |
Setting of ANSI_NULLS at creation time. 1 = True |
ExecIsUpdateTrigger |
Trigger |
UPDATE trigger. 0 = False |
IsIndexed |
Table, view |
Table or view that has an index. 0 = False |
IsIndexable |
Table, view |
Table or view on which an index can be created. 0 = False |
IsInlineFunction |
Function |
Inline function. 0 = Not inline function |
IsQuotedIdentOn |
Transact-SQL function, Transact-SQL procedure, table, Transact-SQL trigger, view, CHECK constraint, DEFAULT definition |
Specifies that the quoted identifier setting for the object is ON. This means double quotation marks delimit identifiers in all expressions involved in the object definition. 1 = ON |
IsSchemaBound |
Function, view |
A schema bound function or view created by using SCHEMABINDING. 0 = Not schema-bound. |
IsSystemTable |
Table |
System table. 1 = True 0 = False |
IsTable |
Table |
Table. 1 = True |
IsUserTable |
Table |
User-defined table. 1 = True 0 = False |
IsView |
View |
View. 0 = False |
OwnerId TableDeleteTrigger |
Any schema-scoped object Table |
Owner of the object. Always 1. Table has a DELETE trigger. Returns 0. |
TableDeleteTriggerCount |
Table |
Table has the specified number of DELETE triggers. Returns 0. |
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. |
TableFullTextBackgroundUpdateIndexOn |
Table |
Table has full-text background update index (autochange tracking) enabled. 0 = FALSE |
TableFulltextCatalogId |
Table |
ID of the full-text catalog in which the full-text index data for the table resides. 0 = Table does not have a full-text index. |
TableFulltextChangeTrackingOn |
Table |
Table has full-text change-tracking enabled. 0 = FALSE |
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. This property does not monitor or count deleted rows. |
TableFulltextFailCount |
Table |
Number of rows Full-Text Search did not index. 0 = The population has completed. |
TableFulltextItemCount |
Table |
Number of rows that were successfully full-text indexed. Returns 0. |
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. |
TableFulltextPendingChanges |
Table |
Number of pending change tracking entries to process. 0 = change tracking is not enabled. |
TableFulltextPopulateStatus TableHasActiveFulltextIndex |
Table Table |
0 = Idle. Table has an active full-text index. 0 = False |
TableHasCheckCnst |
Table |
Table has a CHECK constraint. 0 = False |
TableHasClustIndex |
Table |
Table has a clustered index. 0 = False |
TableHasDefaultCnst |
Table |
Table has a DEFAULT constraint. 0 = False |
TableHasDeleteTrigger |
Table |
Table has a DELETE trigger. 0 = False |
TableHasIdentity |
Table |
Table has an identity column. 0 = False |
TableHasIndex |
Table |
Table has an index of any type. 0 = False |
TableHasInsertTrigger |
Table |
Object has an INSERT trigger. 0 = False |
TableHasNonclustIndex |
Table |
Table has a nonclustered index. 0 = False |
TableHasRowGuidCol |
Table |
Table has a ROWGUIDCOL for a uniqueidentifier column. 0 = False |
TableHasTextImage |
Table |
Table has a text, ntext, or image column. 0 = False |
TableHasUpdateTrigger |
Table |
Object has an UPDATE trigger. 0 = False |
TableHasVarDecimalStorageFormat |
Table |
Table is enabled for vardecimal storage format. 0 = False |
TableInsertTrigger |
Table |
Table has an INSERT trigger. Returns 0. |
TableInsertTriggerCount |
Table |
Table has the specified number of INSERT triggers. Returns 0. |
TableIsFake |
Table |
Table is not real. It is materialized internally on demand by the SQL Server Database Engine. 0 = False |
TableIsLockedOnBulkLoad |
Table |
Table is locked due to a bcp or BULK INSERT job. 0 = False |
TableIsPinned |
Table |
Table is pinned to be held in the data cache. 0 = False |
TableTextInRowLimit |
Table |
Maximum bytes allowed for text in row. Returns 0. |
TableUpdateTrigger |
Table |
Table has an UPDATE trigger. Returns 0. |
TableUpdateTriggerCount |
Table |
The table has the specified number of UPDATE triggers. Returns 0. |
TableHasColumnSet |
Table |
Table has a column set. 0 = False |
Return types#
sql_variant
Example#
SELECT OBJECTPROPERTYEX(OBJECT_ID(N'AdventureWorks2012.HumanResources.vEmployee'), 'IsView');