OBJECTPROPERTY (Transact-SQL)

**APPLIES TO:** SQL Server (starting with 2008) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse

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.

Return Types

int

Exceptions

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

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 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'AdventureWorks2012.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 **AdventureWorks2012** database.

USE AdventureWorks2012;  
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 AdventureWorks2012;  
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: [!INCLUDEssSDWfull] and [!INCLUDEssPDW]

D: Verifying that an object is a table

The following example tests whether dbo.DimReseller is a table in the **AdventureWorksPDW2012** 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)