OBJECTPROPERTY

Transact-SQL Reference

Transact-SQL Reference

OBJECTPROPERTY

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.

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

See Also

COLUMNPROPERTY

CREATE INDEX

Metadata Functions

TYPEPROPERTY