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.

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