OBJECTPROPERTY (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

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.

Transact-SQL syntax conventions

Syntax

OBJECTPROPERTY ( id , property )   

Arguments

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.

Note

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
ExecIsWithNativeCompilation	Transact-SQL Procedure	Applies to: SQL Server 2014 (12.x) and later. Procedure is natively compiled. 1 = True 0 = False Base data type: int
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	Applies to: SQL Server 2008 (10.0.x) and later. 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
IsEncrypted	Transact-SQL function, Transact-SQL procedure, table, Transact-SQL trigger, view	Indicates that the original text of the module statement was converted to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server 2005 (9.x). Users without access to system tables or database files cannot retrieve the obfuscated text. However, the text is available to users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the original procedure from memory at run time. 1 = Encrypted 0 = Not encrypted Base data type: int
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. 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
IsSystemVerified	Object	SQL Server can verify the determinism and precision properties of the object. 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. Note: 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. Nonnull = The database user ID of the object owner.
SchemaId	Any schema-scoped object	Schema ID of the schema to which the object belongs.
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.
TableFullTextMergeStatus	Table	Applies to: SQL Server 2008 (10.0.x) and later. Whether a table that has a full-text index that is currently in merging. 0 = Table does not have a full-text index, or the full-text index is not in merging. 1 = The full-text index is in merging.
TableFullTextBackgroundUpdateIndexOn	Table	Applies to: SQL Server 2008 (10.0.x) and later. Table has full-text background update index (autochange tracking) enabled. 1 = TRUE 0 = FALSE
TableFulltextCatalogId	Table	Applies to: SQL Server 2008 (10.0.x) and later. 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	Applies to: SQL Server 2008 (10.0.x) and later. Table has full-text change-tracking enabled. 1 = TRUE 0 = FALSE
TableFulltextDocsProcessed	Table	Applies to: SQL Server 2008 (10.0.x) and later. 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 (A or B): A) The number of documents processed by insert or update operations since the start of Full, Incremental, or Manual change tracking population. B) The number of rows processed by insert or update operations 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. This property does not monitor or count deleted rows.
TableFulltextFailCount	Table	Applies to: SQL Server 2008 (10.0.x) and later. Number of rows Full-Text Search did not index. 0 = The population has completed. > 0 = One of the following (A or B): A) The number of documents that were not indexed since the start of Full, Incremental, and Manual Update change tracking population. B) 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	Applies to: SQL Server 2008 (10.0.x) and later. Number of rows that were successfully full-text indexed.
TableFulltextKeyColumn	Table	Applies to: SQL Server 2008 (10.0.x) and later. 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	Applies to: SQL Server 2008 (10.0.x) and later. 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	Applies to: SQL Server 2008 (10.0.x) and later. 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	Applies to: SQL Server 2008 (10.0.x) and later. 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
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 Database Engine. 1 = True 0 = False
TableIsLockedOnBulkLoad	Table	Table is locked due to a bcp or BULK INSERT job. 1 = True 0 = False
TableIsMemoryOptimized	Table	Applies to: SQL Server 2014 (12.x) and later. Table is memory optimized 1 = True 0 = False Base data type: int For more information, see In-Memory OLTP (In-Memory Optimization).
TableIsPinned	Table	Table is pinned to be held in the data cache. 0 = False This feature is not supported in SQL Server 2005 (9.x) and later.
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.
TableHasColumnSet	Table	Table has a column set. 0 = False 1 = True For more information, see Use Column Sets.
TableTemporalType	Table	Applies to: SQL Server 2016 (13.x) and later. Specifies the type of table. 0 = non-temporal table 1 = history table for system-versioned table 2 = system-versioned temporal table

Return Types

int

Exceptions

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

Permissions

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.

Remarks

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'AdventureWorks2022.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).

Examples

A. Verifying that an object is a table

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

USE AdventureWorks2022;  
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 that 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 AdventureWorks2022;  
GO  
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.ufnGetProductDealerPrice'), 'IsDeterministic');  
GO  

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

-----  
0

C: Finding the tables that belong to a specific schema

The following example returns all the tables in the dbo schema.

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

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

D: Verifying that an object is a table

The following example tests whether dbo.DimReseller is a table in the AdventureWorksPDW2022 database.

-- Uses AdventureWorks  
  
IF OBJECTPROPERTY (OBJECT_ID(N'dbo.DimReseller'),'ISTABLE') = 1  
   SELECT 'DimReseller is a table.'  
ELSE   
   SELECT 'DimReseller is not a table.';  
GO  

See Also

COLUMNPROPERTY (Transact-SQL)
Metadata Functions (Transact-SQL)
OBJECTPROPERTYEX (Transact-SQL)
ALTER AUTHORIZATION (Transact-SQL)
TYPEPROPERTY (Transact-SQL)
sys.objects (Transact-SQL)