OBJECTPROPERTY
Returns information about schema-scoped objects in the current database. This function cannot be used for objects that are not schema-scoped.
Syntax
OBJECTPROPERTY ( 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 represents the information to be returned for the object specified by id. property can be one of the following values.
| Property name | Object type | Description and values returned |
|---|---|---|
| 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 | Any schema-scoped object | Owner of the object. Always 1. |
| TableDeleteTrigger | Table | 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 | Table | 0 = Idle. |
| TableHasActiveFulltextIndex | Table | 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
int
Example
SELECT OBJECTPROPERTY(OBJECT_ID(N'AdventureWorks2012.HumanResources.vEmployee'), 'IsView');