How OBJECTPROPERTY() Works in SQL Server

Posted on by Ian

In SQL Server, the OBJECTPROPERTY() function returns information about schema-scoped objects in the current database.

These schema-scoped objects are the ones you can see by querying the sys.objects system catalog view. It can’t be used for objects that are not schema-scoped.

You can use OBJECTPROPERTY() to check if an object is a table, view, stored procedure, etc. You can also use it to check if a table has a primary key, foreign key, foreign key reference, etc.

Syntax

The syntax is simple. The function accepts two arguments: the object’s ID, and the property you want returned.

OBJECTPROPERTY ( id , property )

Example 1 – Basic Usage

Here’s an example to demonstrate the basic usage of this function.

SELECT OBJECTPROPERTY('885578193', 'IsTable') AS Result;

Result:

+----------+
| Result   |
|----------|
| 1        |
+----------+

In this case, there’s an object with an ID of 885578193 and it’s a table.

I know that it’s a table because the result of the IsTable property is 1. If the object wasn’t a table, the result here would be 0.

Example 2 – Naming the Object

In the previous example, I knew the ID of the object. In most cases you’ll probably only know the object’s name, but not its ID. In such cases, you can use the OBJECT_ID() function to get the object’s ID, based on its name.

Like this:

SELECT OBJECTPROPERTY(OBJECT_ID(N'Artists'), 'IsTable') AS Result;

Result:

+----------+
| Result   |
|----------|
| 1        |
+----------+

Example 3 – Qualified Object Name

When calling the OBJECT_ID() function, you can also provide a two or three part name (to include the schema name and the database name).

SELECT OBJECTPROPERTY(OBJECT_ID(N'Music.dbo.Artists'), 'IsTable') AS Result;

Result:

+----------+
| Result   |
|----------|
| 1        |
+----------+

However, don’t let that trick you into thinking that OBJECTPROPERTY() will use that database. It won’t (unless it happens to be the same as the current database). As far as it’s concerned, it’s simply receiving an object ID. Forgetting this could lead to a misleading result.

Here’s an example to demonstrate.

USE WideWorldImportersDW;
SELECT 
  OBJECT_ID(N'Music.dbo.Artists') AS [OBJECT_ID],
  OBJECT_NAME(OBJECT_ID(N'Music.dbo.Artists')) AS [OBJECT_NAME],
  OBJECTPROPERTY(OBJECT_ID(N'Music.dbo.Artists'), 'IsTable') AS [IsTable];

USE Music;
SELECT 
  OBJECT_ID(N'Music.dbo.Artists') AS [OBJECT_ID],
  OBJECT_NAME(OBJECT_ID(N'Music.dbo.Artists')) AS [OBJECT_NAME],
  OBJECTPROPERTY(OBJECT_ID(N'Music.dbo.Artists'), 'IsTable') AS [IsTable];

Result:

Changed database context to 'WideWorldImportersDW'.
+-------------+---------------+-----------+
| OBJECT_ID   | OBJECT_NAME   | IsTable   |
|-------------+---------------+-----------|
| 885578193   | CityKey       | 0         |
+-------------+---------------+-----------+
(1 row affected)
Changed database context to 'Music'.
+-------------+---------------+-----------+
| OBJECT_ID   | OBJECT_NAME   | IsTable   |
|-------------+---------------+-----------|
| 885578193   | Artists       | 1         |
+-------------+---------------+-----------+
(1 row affected)

In this example, two different databases happen to have an object with the same ID. One of them is a table and the other isn’t. So we get a negative result in the first query and a positive in the second.

Example 4 – More Properties

Here’s an example that returns more properties.

USE WideWorldImportersDW;
DECLARE @objectId int = OBJECT_ID(N'Dimension.City');
SELECT 
  OBJECTPROPERTY(@objectId, 'OwnerId') AS OwnerId,
  OBJECTPROPERTY(@objectId, 'SchemaId') AS SchemaId,
  OBJECTPROPERTY(@objectId, 'IsTable') AS IsTable,
  OBJECTPROPERTY(@objectId, 'TableHasPrimaryKey') AS TableHasPrimaryKey,
  OBJECTPROPERTY(@objectId, 'TableHasForeignKey') AS TableHasForeignKey,
  OBJECTPROPERTY(@objectId, 'TableHasForeignRef') AS TableHasForeignRef,
  OBJECTPROPERTY(@objectId, 'TableHasIdentity') AS TableHasIdentity;

Result (using vertical output):

OwnerId            | 1
SchemaId           | 6
IsTable            | 1
TableHasPrimaryKey | 1
TableHasForeignKey | 0
TableHasForeignRef | 1
TableHasIdentity   | 0

These are just some of the 103 properties that you can query OBJECTPROPERTY() for. See below for a full list.

Example 5 – In a WHERE Clause

You can use OBJECTPROPERTY() in a WHERE clause if required.

In this example, I run two queries: one that returns tables that have a foreign key, and one that returns tables that are referenced by a foreign key.

USE WideWorldImporters;
SELECT 
  SCHEMA_NAME(schema_id) AS [Schema], 
  name AS [Table]
FROM sys.tables
WHERE OBJECTPROPERTY(object_id, 'TableHasForeignKey') = 1;

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema], 
  name AS [Table]
FROM sys.tables
WHERE OBJECTPROPERTY(object_id, 'TableHasForeignRef') = 1;

Result:

Changed database context to 'WideWorldImporters'.
+-------------+-----------------------+
| Schema      | Table                 |
|-------------+-----------------------|
| Warehouse   | Colors                |
| Sales       | OrderLines            |
| Warehouse   | PackageTypes          |
| Warehouse   | StockGroups           |
| Warehouse   | StockItemStockGroups  |
| Application | StateProvinces        |
| Sales       | CustomerTransactions  |
| Application | Cities                |
| Application | SystemParameters      |
| Sales       | InvoiceLines          |
| Purchasing  | Suppliers             |
| Warehouse   | StockItemTransactions |
| Sales       | Customers             |
| Purchasing  | PurchaseOrders        |
| Sales       | Orders                |
| Application | People                |
| Warehouse   | StockItems            |
| Application | Countries             |
| Warehouse   | StockItemHoldings     |
| Purchasing  | PurchaseOrderLines    |
| Application | DeliveryMethods       |
| Application | PaymentMethods        |
| Purchasing  | SupplierTransactions  |
| Application | TransactionTypes      |
| Sales       | SpecialDeals          |
| Purchasing  | SupplierCategories    |
| Sales       | BuyingGroups          |
| Sales       | Invoices              |
| Sales       | CustomerCategories    |
+-------------+-----------------------+
(29 rows affected)
+-------------+--------------------+
| Schema      | Table              |
|-------------+--------------------|
| Warehouse   | Colors             |
| Warehouse   | PackageTypes       |
| Warehouse   | StockGroups        |
| Application | StateProvinces     |
| Application | Cities             |
| Purchasing  | Suppliers          |
| Sales       | Customers          |
| Purchasing  | PurchaseOrders     |
| Sales       | Orders             |
| Application | People             |
| Warehouse   | StockItems         |
| Application | Countries          |
| Application | DeliveryMethods    |
| Application | PaymentMethods     |
| Application | TransactionTypes   |
| Purchasing  | SupplierCategories |
| Sales       | BuyingGroups       |
| Sales       | Invoices           |
| Sales       | CustomerCategories |
+-------------+--------------------+
(19 rows affected)

Full List of Properties

Here’s a full list of properties supported by OBJECTPROPERTY():

  • CnstIsClustKey
  • CnstIsColumn
  • CnstIsDeleteCascade
  • CnstIsDisabled
  • CnstIsNonclustKey
  • CnstIsNotRepl
  • CnstIsNotTrusted
  • CnstIsUpdateCascade
  • ExecIsAfterTrigger
  • ExecIsAnsiNullsOn
  • ExecIsDeleteTrigger
  • ExecIsFirstDeleteTrigger
  • ExecIsFirstInsertTrigger
  • ExecIsFirstUpdateTrigger
  • ExecIsInsertTrigger
  • ExecIsInsteadOfTrigger
  • ExecIsLastDeleteTrigger
  • ExecIsLastInsertTrigger
  • ExecIsLastUpdateTrigger
  • ExecIsQuotedIdentOn
  • ExecIsStartup
  • ExecIsTriggerDisabled
  • ExecIsTriggerNotForRepl
  • ExecIsUpdateTrigger
  • ExecIsWithNativeCompilation
  • HasAfterTrigger
  • HasDeleteTrigger
  • HasInsertTrigger
  • HasInsteadOfTrigger
  • HasUpdateTrigger
  • IsAnsiNullsOn
  • IsCheckCnst
  • IsConstraint
  • IsDefault
  • IsDefaultCnst
  • IsDeterministic
  • IsEncrypted
  • IsExecuted
  • IsExtendedProc
  • IsForeignKey
  • IsIndexed
  • IsIndexable
  • IsInlineFunction
  • IsMSShipped
  • IsPrimaryKey
  • IsProcedure
  • IsQuotedIdentOn
  • IsQueue
  • IsReplProc
  • IsRule
  • IsScalarFunction
  • IsSchemaBound
  • IsSystemTable
  • IsSystemVerified
  • IsTable
  • IsTableFunction
  • IsTrigger
  • IsUniqueCnst
  • IsUserTable
  • IsView
  • OwnerId
  • SchemaId
  • TableDeleteTrigger
  • TableDeleteTriggerCount
  • TableFullTextMergeStatus
  • TableFullTextBackgroundUpdateIndexOn
  • TableFulltextCatalogId
  • TableFulltextChangeTrackingOn
  • TableFulltextDocsProcessed
  • TableFulltextFailCount
  • TableFulltextItemCount
  • TableFulltextKeyColumn
  • TableFulltextPendingChanges
  • TableFulltextPopulateStatus
  • TableHasActiveFulltextIndex
  • TableHasCheckCnst
  • TableHasClustIndex
  • TableHasDefaultCnst
  • TableHasDeleteTrigger
  • TableHasForeignKey
  • TableHasForeignRef
  • TableHasIdentity
  • TableHasIndex
  • TableHasInsertTrigger
  • TableHasNonclustIndex
  • TableHasPrimaryKey
  • TableHasRowGuidCol
  • TableHasTextImage
  • TableHasTimestamp
  • TableHasUniqueCnst
  • TableHasUpdateTrigger
  • TableHasVarDecimalStorageFormat
  • TableInsertTrigger
  • TableInsertTriggerCount
  • TableIsFake
  • TableIsLockedOnBulkLoad
  • TableIsMemoryOptimized
  • TableIsPinned
  • TableTextInRowLimit
  • TableUpdateTrigger
  • TableUpdateTriggerCount
  • TableHasColumnSet
  • TableTemporalType

See the Microsoft documentation for a detailed explanation of each property.