Transact-SQL Reference

OBJECTPROPERTY

Returns information about objects in the current database.

Syntax

OBJECTPROPERTY ( id , property )

Arguments

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.

Note Unless noted otherwise, the value NULL is returned when property is not a valid property name.

Property name	Object type	Description and values returned
CnstIsClustKey	Constraint	A primary key with a clustered index. 1 = True 0 = False
CnstIsColumn	Constraint	COLUMN constraint. 1 = True 0 = False
CnstIsDeleteCascade	Constraint	A foreign key constraint with the ON DELETE CASCADE option.
CnstIsDisabled	Constraint	Disabled constraint. 1 = True 0 = False
CnstIsNonclustKey	Constraint	A primary key with a nonclustered index. 1 = True 0 = False
CnstIsNotTrusted	Constraint	Constraint was enabled without checking existing rows, so constraint may not hold for all rows. 1 = True 0 = False
CnstIsNotRepl	Constraint	The constraint is defined with the NOT FOR REPLICATION keywords.
CnstIsUpdateCascade	Constraint	A foreign key constraint with the ON UPDATE CASCADE option.
ExecIsAfterTrigger	Trigger	AFTER trigger.
ExecIsAnsiNullsOn	Procedure, Trigger, View	The setting of ANSI_NULLS at creation time. 1 = True 0 = False
ExecIsDeleteTrigger	Trigger	DELETE trigger. 1 = True 0 = False
ExecIsFirstDeleteTrigger	Trigger	The first trigger fired when a DELETE is executed against the table.
ExecIsFirstInsertTrigger	Trigger	The first trigger fired when an INSERT is executed against the table.
ExecIsFirstUpdateTrigger	Trigger	The first trigger fired when an UPDATE is executed against the table.
ExecIsInsertTrigger	Trigger	INSERT trigger. 1 = True 0 = False
ExecIsInsteadOfTrigger	Trigger	INSTEAD OF trigger.
ExecIsLastDeleteTrigger	Trigger	The last trigger fired when a DELETE is executed against the table.
ExecIsLastInsertTrigger	Trigger	The last trigger fired when an INSERT is executed against the table.
ExecIsLastUpdateTrigger	Trigger	The last trigger fired when an UPDATE is executed against the table.
ExecIsQuotedIdentOn	Procedure, Trigger, View	The setting of QUOTED_IDENTIFIER at creation time. 1 = True 0 = False
ExecIsStartup	Procedure	Startup procedure. 1 = True 0 = False
ExecIsTriggerDisabled	Trigger	Disabled trigger. 1 = True 0 = False
ExecIsUpdateTrigger	Trigger	UPDATE trigger. 1 = True 0 = False
HasAfterTrigger	Table, View	Table or view has an AFTER trigger. 1 = True 0 = False
HasInsertTrigger	Table, View	Table or view has an INSERT trigger. 1 = True 0 = False
HasInsteadOfTrigger	Table, View	Table or view has an INSTEAD OF trigger. 1 = True 0 = False
HasUpdateTrigger	Table, View	Table or view has an UPDATE trigger. 1 = True 0 = False
IsAnsiNullsOn	Function, Procedure, Table, 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 = ON 0 = OFF
IsCheckCnst	Any	CHECK constraint. 1 = True 0 = False
IsConstraint	Any	Constraint. 1 = True 0 = False
IsDefault	Any	Bound default. 1 = True 0 = False
IsDefaultCnst	Any	DEFAULT constraint. 1 = True 0 = False
IsDeterministic	Function, View	The determinism property of the function. Applies only to scalar- and table-valued functions. 1 = Deterministic 0 = Not Deterministic NULL = Not a scalar- or table-valued function, or invalid object ID.
IsExecuted	Any	Specifies how this object can be executed (view, procedure, or trigger). 1 = True 0 = False
IsExtendedProc	Any	Extended procedure. 1 = True 0 = False
IsForeignKey	Any	FOREIGN KEY constraint. 1 = True 0 = False
IsIndexed	Table, View	A table or view with an index.
IsIndexable	Table, View	A table or view on which an index may be created.
IsInlineFunction	Function	Inline function. 1 = Inline function 0 = Not inline function NULL = Not a function, or invalid object ID.
IsMSShipped	Any	An object created during installation of Microsoft® SQL Server™ 2000. 1 = True 0 = False
IsPrimaryKey	Any	PRIMARY KEY constraint. 1 = True 0 = False
IsProcedure	Any	Procedure. 1 = True 0 = False
IsQuotedIdentOn	Function, Procedure, Table, Trigger, View	Specifies that the quoted identifier setting for the table is ON, meaning double quotation marks delimit identifiers in all expressions involved in the table definition. 1 = ON 0 = OFF
IsReplProc	Any	Replication procedure. 1 = True 0 = False
IsRule	Any	Bound rule. 1 = True 0 = False
IsScalarFunction	Function	Scalar-valued function. 1 = Scalar-valued 0 = Table-valued NULL = Not a function, or invalid object ID.
IsSchemaBound	Function, View	A schema bound function or view created with SCHEMABINDING. 1 = Schema-bound 0 = Not schema-bound NULL = Not a function or a view, or invalid object ID.
IsSystemTable	Table	System table. 1 = True 0 = False
IsTable	Table	Table. 1 = True 0 = False
IsTableFunction	Function	Table-valued function. 1 = Table-valued 0 = Scalar-valued NULL = Not a function, or invalid object ID.
IsTrigger	Any	Trigger. 1 = True 0 = False
IsUniqueCnst	Any	UNIQUE constraint. 1 = True 0 = False
IsUserTable	Table	User-defined table. 1 = True 0 = False
IsView	View	View. 1 = True 0 = False
OwnerId	Any	Owner of the object. Nonnull = The database user ID of the object owner. NULL = Invalid input.
TableDeleteTrigger	Table	Table has a DELETE trigger. >1 = ID of first trigger with given type.
TableDeleteTriggerCount	Table	The table has the specified number of DELETE triggers. >1 = ID of first trigger with given type. NULL = Invalid input.
TableFullTextBackgroundUpdateIndexOn	Table	The table has full-text background update index enabled. 1 = TRUE 0 = FALSE
TableFulltextCatalogId	Table	The 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 is not full-text indexed.
TableFullTextChangeTrackingOn	Table	The table has full-text change-tracking enabled. 1 = TRUE 0 = FALSE
TableFulltextKeyColumn	Table	The ID of the column associated with the single-column unique index that is participating in the full-text index definition. 0 = Table is not full-text indexed.
TableFullTextPopulateStatus	Table	0 = No population 1 = Full population 2 = Incremental population
TableHasActiveFulltextIndex	Tables	The table has an active full-text index. 1 = True 0 = False
TableHasCheckCnst	Table	The table has a CHECK constraint. 1 = True 0 = False
TableHasClustIndex	Table	The table has a clustered index. 1 = True 0 = False
TableHasDefaultCnst	Table	The table has a DEFAULT constraint. 1 = True 0 = False
TableHasDeleteTrigger	Table	The table has a DELETE trigger. 1 = True 0 = False
TableHasForeignKey	Table	The table has a FOREIGN KEY constraint. 1 = True 0 = False
TableHasForeignRef	Table	Table is referenced by a FOREIGN KEY constraint. 1 = True 0 = False
TableHasIdentity	Table	The table has an identity column. 1 = True 0 = False
TableHasIndex	Table	The table has an index of any type. 1 = True 0 = False
TableHasInsertTrigger	Table	The object has an Insert trigger. 1 = True 0 = False NULL = Invalid input.
TableHasNonclustIndex	Table	The table has a nonclustered index. 1 = True 0 = False
TableHasPrimaryKey	Table	The table has a primary key. 1 = True 0 = False
TableHasRowGuidCol	Table	The table has a ROWGUIDCOL for a uniqueidentifier column. 1 = True 0 = False
TableHasTextImage	Table	The table has a text column. 1 = True 0 = False
TableHasTimestamp	Table	The table has a timestamp column. 1 = True 0 = False
TableHasUniqueCnst	Table	The table has a UNIQUE constraint. 1 = True 0 = False
TableHasUpdateTrigger	Table	The object has an Update trigger. 1 = True 0 = False
TableInsertTrigger	Table	The table has an INSERT trigger. >1 = ID of first trigger with given type.
TableInsertTriggerCount	Table	The table has the specified number of INSERT triggers. >1 = ID of first trigger with given type.
TableIsFake	Table	The table is not real. It is materialized internally on demand by SQL Server. 1 = True 0 = False
TableIsPinned	Table	The table is pinned to be held in the data cache. 1 = True 0 = False
TableTextInRowLimit	Table	The maximum bytes allowed for text in row, or 0 if text in row option is not set.
TableUpdateTrigger	Table	The table has an UPDATE trigger. >1 = ID of first trigger with given type.
TableUpdateTriggerCount	Table	The table has the specified number of UPDATE triggers. >1 = ID of first trigger with given type.

Return Types

int

Remarks

OBJECTPROPERTY(view_id,'IsIndexable') may consume significant computer resources because evaluation of IsIndexable property requires the parsing of view definition, normalization, and partial optimization.

OBJECTPROPERTY(table_id, 'TableHasActiveFulltextIndex') will return '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.

When the last column in an index is dropped, the index becomes inactive.

The actual creation of index still might fail if certain index key requirements are not met. See CREATE INDEX for details.

Examples

A. To find out if authors is a table

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'

B. To determine if text in row is enabled on a table

This example tests whether the text in row option is enabled in the authors table so that text, ntext, or image data can be stored in its data row.

USE pubs
SELECT OBJECTPROPERTY(OBJECT_ID('authors'),'TableTextInRowLimit')

The result set shows that text in row is not enabled on the table.

-----
0

C. To determine if a scalar-valued user-defined function is deterministic

This example tests whether the user-defined scalar-valued function fn_CubicVolume, which returns a decimal, is deterministic.

CREATE FUNCTION fn_CubicVolume
-- Input dimensions in centimeters.
   (@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
   @CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
WITH SCHEMABINDING
AS
BEGIN
   RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END

--Is it a deterministic function?
SELECT OBJECTPROPERTY(OBJECT_ID('fn_CubicVolume'), 'IsDeterministic')

The result set shows that fn_CubicVolume is a deterministic function.

-----
1