OBJECTPROPERTY (T-SQL)

Returns information about objects in the current database.

Syntax

OBJECTPROPERTY(id, property)

Arguments

id

Is an expression containing the ID of the object in the current database. id is int.

property

Is an expression containing the information to be returned for the object specified by id. property can be one of these values.

 

Value	Object type	Description
CnstIsClustKey	Constraint	Primary key with a clustered index.
CnstIsColumn	Constraint	COLUMN constraint.
CnstIsDisabled	Constraint	Disabled constraint.
CnstIsNonclustKey	Constraint	Primary key with a nonclustered index.
CnstIsNotRepl	Constraint	Constraint defined with the NOT FOR REPLICATION keywords.
ExecIsAnsiNullsOn	Procedure, Trigger, View	Setting of ANSI_NULLS at creation time.
ExecIsDeleteTrigger	Trigger	A DELETE trigger.
ExecIsInsertTrigger	Trigger	An INSERT trigger.
ExecIsQuotedIdentOn	Procedure, Trigger, View	Setting of QUOTED_IDENTIFIER at creation time.
ExecIsStartup	Procedure	Startup procedure.
ExecIsTriggerDisabled	Trigger	Disabled trigger.
ExecIsUpdateTrigger	Trigger	UPDATE trigger.
IsCheckCnst	Any	CHECK constraint.
IsConstraint	Any	Constraint.
IsDefault	Any	Bound default.
IsDefaultCnst	Any	DEFAULT constraint.
IsExecuted	Any	How this object can be executed (view, procedure, or trigger).
IsExtendedProc	Any	Extended procedure.
IsForeignKey	Any	FOREIGN KEY constraint.
IsMSShipped	Any	Object created during installation of Microsoft® SQL Server™.
IsPrimaryKey	Any	PRIMARY KEY constraint.
IsProcedure	Any	Procedure.
IsReplProc	Any	Replication procedure.
IsRule	Any	Bound rule.
IsSystemTable	Table	System table.
IsTable	Table	Table.
IsTrigger	Any	Trigger.
IsUniqueCnst	Any	UNIQUE constraint.
IsUserTable	Table	User-defined table.
IsView	View	View.
OwnerId	Any	Owner of the object.
TableDeleteTrigger	Table	Table has a DELETE trigger.
TableDeleteTriggerCount	Table	Table has the specified number of DELETE triggers.
TableFulltextCatalogId	Table	ID of the full-text catalog in which the full-text index data for the table resides.
TableFulltextKeyColumn	Table	ID of the column associated with the single-column unique index that is participating in the full-text index definition.
TableHasActiveFulltextIndex	Tables	Table is full-text indexed.
TableHasCheckCnst	Table	Table has a CHECK constraint.
TableHasClustIndex	Table	Table has a clustered index.
TableHasDefaultCnst	Table	Table has a DEFAULT constraint.
TableHasDeleteTrigger	Table	Table has a DELETE trigger.
TableHasForeignKey	Table	Table has a FOREIGN KEY constraint.
TableHasForeignRef	Table	Table is referenced by a FOREIGN KEY constraint.
TableHasIdentity	Table	Table has an identity column.
TableHasIndex	Table	Table has an index of any type.
TableHasInsertTrigger	Table	Table has an INSERT trigger.
TableHasNonclustIndex	Table	Table has a nonclustered index.
TableHasPrimaryKey	Table	Table has a primary key.
TableHasRowGuidCol	Table	Table has a ROWGUIDCOL for a uniqueidentifier column.
TableHasTextImage	Table	Table has a text column.
TableHasTimestamp	Table	Table has a timestamp column.
TableHasUniqueCnst	Table	Table has a UNIQUE constraint.
TableHasUpdateTrigger	Table	Table has an UPDATE trigger.
TableInsertTrigger	Table	Table has an INSERT trigger.
TableInsertTriggerCount	Table	Table has the specified number of INSERT triggers.
TableIsFake	Table	Table is not real. It is materialized internally on demand by SQL Server.
TableIsPinned	Table	Table is pinned to be held in the data cache.
TableUpdateTrigger	Table	Table has an UPDATE trigger.
TableUpdateTriggerCount	Table	Table has the specified number of UPDATE triggers.
TriggerDeleteOrder	Table	Firing order of this DELETE trigger.
TriggerInsertOrder	Table	Firing order of this INSERT trigger.
TriggerUpdateOrder	Table	Firing order of this UPDATE trigger.

The table shows the values that can be returned for the types of properties.

 

Property names	Values returned
CnstIsClustKey CnstIsColumn CnstIsDisabled CnstIsNonclustKey CnstIsNotRepl	1 = True 0 = False NULL = Invalid input.
ExecIsAnsiNullsOn ExecIsDeleteTrigger ExecIsGlobalProc ExecIsInsertTrigger ExecIsQuotedIdentOn ExecIsStartup ExecIsDisabledTrigger ExecIsUpdateTrigger	1 = True 0 = False NULL = Invalid input.
IsCheckCnst IsConstraint IsDefault IsDefaultCnst IsExecuted IsExtendedProc IsForeignKey IsMSShipped IsPrimaryKey IsProcedure IsReplProc IsRule IsSystemTable IsTable IsTrigger IsUniqueCnst IsUserTable IsView	1 = True 0 = False NULL = Invalid input.
OwnerId	Nonnull = The database user ID of the object owner. NULL = Invalid input.
TableDeleteTrigger TableDeleteTriggerCount TableInsertTrigger TableInsertTriggerCount TableUpdateTrigger TableUpdateTriggerCount	>1 = ID of first trigger with given type. NULL = Invalid input.
TableHasCheckCnst TableHasActiveFulltextIndex TableHasClustIndex TableHasDefaultCnst TableHasDeleteTrigger TableHasForeignKey TableHasForeignRef TableHasIdentity TableHasIndex TableHasInsertTrigger TableHasNonclustIndex TableHasPrimaryKey TableHasRowGuidCol TableHasTextImage TableHasTimestamp TableHasUniqueCnst TableHasUpdateTrigger TableIsFake TableIsPinned TriggerDeleteOrder TriggerInsertOrder TriggerUpdateOrder	1 = True 0 = False NULL = Invalid input.
TableFulltextCatalogId TableFulltextKeyColumn	Nonzero = Full-text catalog ID. Nonzero = ID associated with the unique index that identifies the rows in a full-text indexed table. 0 = Table is not full-text indexed. NULL = Invalid input.

Return Types

int

Examples

This example tests whether authors is a table.

IF OBJECTPROPERTY ( object_id('authors'),'ISTABLE') = 1

    print 'Authors is a table'

ELSE IF OBJECTPROPERTY ( object_id('authors'),'ISTABLE') = 0

    print 'Authors is not a table'

ELSE IF OBJECTPROPERTY ( object_id('authors'),'ISTABLE') IS NULL

    print 'ERROR: Authors is not an object'

See Also