Export (0) Print
Expand All
Expand Minimize
4 out of 7 rated this helpful - Rate this topic

OBJECTPROPERTY (Transact-SQL)

Updated: 12 December 2006

Returns information about schema-scoped objects in the current database. For a list of schema-scoped objects, see sys.objects (Transact-SQL). This function cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications.

Topic link icon Transact-SQL Syntax Conventions


OBJECTPROPERTY ( id , property ) 
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.

ms176105.note(en-US,SQL.90).gifNote:
Unless noted otherwise, NULL is returned when property is not a valid property name, id is not a valid object ID, id is an unsupported object type for the specified property, or the caller does not have permission to view the object's metadata.

Property name Object type Description and values returned

CnstIsClustKey

Constraint

PRIMARY KEY constraint with a clustered index.

1 = True

0 = False

CnstIsColumn

Constraint

CHECK, DEFAULT, or FOREIGN KEY constraint on a single column.

1 = True

0 = False

CnstIsDeleteCascade

Constraint

FOREIGN KEY constraint with the ON DELETE CASCADE option.

1 = True

0 = False

CnstIsDisabled

Constraint

Disabled constraint.

1 = True

0 = False

CnstIsNonclustKey

Constraint

PRIMARY KEY or UNIQUE constraint with a nonclustered index.

1 = True

0 = False

CnstIsNotRepl

Constraint

Constraint is defined by using the NOT FOR REPLICATION keywords.

1 = True

0 = False

CnstIsNotTrusted

Constraint

Constraint was enabled without checking existing rows; therefore, the constraint may not hold for all rows.

1 = True

0 = False

CnstIsUpdateCascade

Constraint

FOREIGN KEY constraint with the ON UPDATE CASCADE option.

1 = True

0 = False

ExecIsAfterTrigger

Trigger

AFTER trigger.

1 = True

0 = False

ExecIsAnsiNullsOn

Transact-SQL function, Transact-SQL procedure, Transact-SQL trigger, view

Setting of ANSI_NULLS at creation time.

1 = True

0 = False

ExecIsDeleteTrigger

Trigger

DELETE trigger.

1 = True

0 = False

ExecIsFirstDeleteTrigger

Trigger

First trigger fired when a DELETE is executed against the table.

1 = True

0 = False

ExecIsFirstInsertTrigger

Trigger

First trigger fired when an INSERT is executed against the table.

1 = True

0 = False

ExecIsFirstUpdateTrigger

Trigger

First trigger fired when an UPDATE is executed against the table.

1 = True

0 = False

ExecIsInsertTrigger

Trigger

INSERT trigger.

1 = True

0 = False

ExecIsInsteadOfTrigger

Trigger

INSTEAD OF trigger.

1 = True

0 = False

ExecIsLastDeleteTrigger

Trigger

Last trigger fired when a DELETE is executed against the table.

1 = True

0 = False

ExecIsLastInsertTrigger

Trigger

Last trigger fired when an INSERT is executed against the table.

1 = True

0 = False

ExecIsLastUpdateTrigger

Trigger

Last trigger fired when an UPDATE is executed against the table.

1 = True

0 = False

ExecIsQuotedIdentOn

Transact-SQL function, Transact-SQL procedure, Transact-SQL trigger, view

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

ExecIsTriggerNotForRepl

Trigger

Trigger defined as NOT FOR REPLICATION.

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

HasDeleteTrigger

Table, view

Table or view has a DELETE 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

Transact-SQL function, Transact-SQL procedure, table, Transact-SQL trigger, view

Specifies that the ANSI NULLS option setting for the table is ON. This means 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 = True

0 = False

IsCheckCnst

Any schema-scoped object

CHECK constraint.

1 = True

0 = False

IsConstraint

Any schema-scoped object

Is a single column CHECK, DEFAULT, or FOREIGN KEY constraint on a column or table.

1 = True

0 = False

IsDefault

Any schema-scoped object

Bound default.

1 = True

0 = False

IsDefaultCnst

Any schema-scoped object

DEFAULT constraint.

1 = True

0 = False

IsDeterministic

Function, view

The determinism property of the function or view.

1 = Deterministic

0 = Not Deterministic

IsExecuted

Any schema-scoped object

Object can be executed (view, procedure, function, or trigger).

1 = True

0 = False

IsExtendedProc

Any schema-scoped object

Extended procedure.

1 = True

0 = False

IsForeignKey

Any schema-scoped object

FOREIGN KEY constraint.

1 = True

0 = False

IsIndexed

Table, view

Table or view that has an index.

1 = True

0 = False

IsIndexable

Table, view

Table or view on which an index can be created.

1 = True

0 = False

IsInlineFunction

Function

Inline function.

1 = Inline function

0 = Not inline function

IsMSShipped

Any schema-scoped object

Object created during installation of SQL Server 2005.

1 = True

0 = False

IsPrimaryKey

Any schema-scoped object

PRIMARY KEY constraint.

1 = True

0 = False

NULL = Not a function, or object ID is not valid.

IsProcedure

Any schema-scoped object

Procedure.

1 = True

0 = False

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

0 = OFF

IsQueue

Any schema-scoped object

Service Broker Queue

1 = True

0 = False

IsReplProc

Any schema-scoped object

Replication procedure.

1 = True

0 = False

IsRule

Any schema-scoped object

Bound rule.

1 = True

0 = False

IsScalarFunction

Function

Scalar-valued function.

1 = Scalar-valued function

0 = Not scalar-valued function

IsSchemaBound

Function, view

A schema bound function or view created by using SCHEMABINDING.

1 = Schema-bound

0 = Not schema-bound.

IsSystemTable

Table

System table.

1 = True

0 = False

IsTable

Table

Table.

1 = True

0 = False

IsTableFunction

Function

Table-valued function.

1 = Table-valued function

0 = Not table-valued function

IsTrigger

Any schema-scoped object

Trigger.

1 = True

0 = False

IsUniqueCnst

Any schema-scoped object

UNIQUE constraint.

1 = True

0 = False

IsUserTable

Table

User-defined table.

1 = True

0 = False

IsView

View

View.

1 = True

0 = False

OwnerId

Any schema-scoped object

Owner of the object.

The schema owner is not necessarily the object owner. For example, child objects (those where parent_object_id is nonnull) will always return the same owner ID as the parent.
ms176105.note(en-US,SQL.90).gifNote:

Nonnull = The database user ID of the object owner.

TableDeleteTrigger

Table

Table has a DELETE trigger.

>1 = ID of first trigger with the specified type.

TableDeleteTriggerCount

Table

Table has the specified number of DELETE triggers.

>0 = The number of DELETE triggers.

TableFullTextBackgroundUpdateIndexOn

Table

Table has full-text background update index (autochange tracking) enabled.

1 = TRUE

0 = FALSE

TableFulltextCatalogId

Table

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 does not have a full-text index.

TableFulltextChangeTrackingOn

Table

Table has full-text change-tracking enabled.

1 = TRUE

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.

> 0 = One of the following:

  • The number of documents processed since the start of Full, Incremental, or Manual change tracking population.
  • The number of rows processed since change tracking with background update index population was enabled, the full-text index schema changed, the full-text catalog rebuilt, or the instance of SQL Server restarted, and so on.

NULL = Table does not have a full-text index.

TableFulltextFailCount

Table

Number of rows Full-Text Search did not index.

0 = The population has completed.

> 0 = One of the following:

  • The number of documents that were not indexed since the start of Full, Incremental, and Manual Update change tracking population.
  • For change tracking with background update index, the number of rows that were not indexed since the start of the population, or the restart of the population. This could be caused by a schema change, rebuild of the catalog, server restart, and so on.

NULL = Table does not have a full-text index.

TableFulltextItemCount

Table

Number of rows that were successfully full-text indexed.

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.

NULL = Table does not have a full-text index.

TableFulltextPopulateStatus

Table

0 = Idle.

1 = Full population is in progress.

2 = Incremental population is in progress.

3 = Propagation of tracked changes is in progress.

4 = Background update index is in progress, such as autochange tracking.

5 = Full-text indexing is throttled or paused.

TableHasActiveFulltextIndex

Table

Table has an active full-text index.

1 = True

0 = False

TableHasCheckCnst

Table

Table has a CHECK constraint.

1 = True

0 = False

TableHasClustIndex

Table

Table has a clustered index.

1 = True

0 = False

TableHasDefaultCnst

Table

Table has a DEFAULT constraint.

1 = True

0 = False

TableHasDeleteTrigger

Table

Table has a DELETE trigger.

1 = True

0 = False

TableHasForeignKey

Table

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

Table has an identity column.

1 = True

0 = False

TableHasIndex

Table

Table has an index of any type.

1 = True

0 = False

TableHasInsertTrigger

Table

Object has an INSERT trigger.

1 = True

0 = False

TableHasNonclustIndex

Table

Table has a nonclustered index.

1 = True

0 = False

TableHasPrimaryKey

Table

Table has a primary key.

1 = True

0 = False

TableHasRowGuidCol

Table

Table has a ROWGUIDCOL for a uniqueidentifier column.

1 = True

0 = False

TableHasTextImage

Table

Table has a text, ntext, or image column.

1 = True

0 = False

TableHasTimestamp

Table

Table has a timestamp column.

1 = True

0 = False

TableHasUniqueCnst

Table

Table has a UNIQUE constraint.

1 = True

0 = False

TableHasUpdateTrigger

Table

Object has an UPDATE trigger.

1 = True

0 = False

TableHasVarDecimalStorageFormat

Table

Table is enabled for vardecimal storage format.

1 = True

0 = False

For information on vardecimal storage format, see Storing Decimal Data As Variable Length.

TableInsertTrigger

Table

Table has an INSERT trigger.

>1 = ID of first trigger with the specified type.

TableInsertTriggerCount

Table

Table has the specified number of INSERT triggers.

>0 = The number of INSERT triggers.

TableIsFake

Table

Table is not real. It is materialized internally on demand by the SQL Server 2005 Database Engine.

1 = True

0 = False

TableIsLockedOnBulkLoad

Table

Table is locked due to a bcp or BULK INSERT job.

1 = True

0 = False

TableIsPinned

Table

Table is pinned to be held in the data cache.

0 = False

This feature is not supported in SQL Server 2005.

TableTextInRowLimit

Table

Maximum bytes allowed for text in row.

0 if text in row option is not set.

TableUpdateTrigger

Table

Table has an UPDATE trigger.

> 1 = ID of first trigger with the specified type.

TableUpdateTriggerCount

Table

The table has the specified number of UPDATE triggers.

> 0 = The number of UPDATE triggers.

Returns NULL on error or if a caller does not have permission to view the object.

In SQL Server 2005, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as OBJECTPROPERTY may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration and Troubleshooting Metadata Visibility.

The Database Engine assumes that object_id is in the current database context. A query that references an object_id in another database will return NULL or incorrect results. For example, in the following query the current database context is the master database. The Database Engine will try to return the property value for the specified object_id in that database instead of the database specified in the query. The query returns incorrect results because the view vEmployee is not in the master database.

USE master;
GO
SELECT OBJECTPROPERTY(OBJECT_ID(N'AdventureWorks.HumanResources.vEmployee'), 'IsView');
GO

OBJECTPROPERTY(view_id, 'IsIndexable') may consume significant computer resources because evaluation of IsIndexable property requires the parsing of view definition, normalization, and partial optimization. Although the IsIndexable property identifies tables or views that can be indexed, the actual creation of the index still might fail if certain index key requirements are not met. For more information, see CREATE INDEX (Transact-SQL).

OBJECTPROPERTY(table_id, 'TableHasActiveFulltextIndex') will return a value of 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 a table is created, the QUOTED IDENTIFIER option is always stored as ON in the metadata of the table, even if the option is set to OFF when the table is created. Therefore, OBJECTPROPERTY(table_id, 'IsQuotedIdentOn') will always return a value of 1 (true).

A. Verifying an object is a table

The following example tests whether UnitMeasure is a table in the AdventureWorks database.

USE AdventureWorks;
GO
IF OBJECTPROPERTY (OBJECT_ID(N'Production.UnitMeasure'),'ISTABLE') = 1
   PRINT 'UnitMeasure is a table.'
ELSE IF OBJECTPROPERTY (OBJECT_ID(N'Production.UnitMeasure'),'ISTABLE') = 0
   PRINT 'UnitMeasure is not a table.'
ELSE IF OBJECTPROPERTY (OBJECT_ID(N'Production.UnitMeasure'),'ISTABLE') IS NULL
   PRINT 'ERROR: UnitMeasure is not a valid object.';
GO

B. Verifying a scalar-valued user-defined function is deterministic

The following example tests whether the user-defined scalar-valued function ufnGetProductDealerPrice, which returns a money value, is deterministic.

USE AdventureWorks;
GO
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.ufnGetProductDealerPrice'), 'IsDeterministic');
GO

The result set shows that ufnGetProductDealerPrice is not a deterministic function.

-----
0

C. Finding the objects that belong to a specific schema

The following example uses the SchemaId property to return all the objects that belong to the schema Production.

USE AdventureWorks;
GO
SELECT name, object_id, type_desc
FROM sys.objects 
WHERE OBJECTPROPERTY(object_id, N'SchemaId') = SCHEMA_ID(N'Production')
ORDER BY type_desc, name;
GO

Release History

12 December 2006

New content:
  • Added the TableHasVarDecimalStorageFormat property, available in Service Pack 2.

17 July 2006

New content:
  • Added the Exceptions section.

5 December 2005

Changed content:
  • Corrected the definition of the IsTableFunction and IsScalarFunction properties.
  • Modified example C.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.