By: Manvendra Singh | Updated: 2022-09-30 | Comments | Related: > Functions System
Problem
SQL Server objects have many different property attributes and in this article, we look at how to use the OBJECTPROPERTY() system function to return various data about objects that exist in a database such as tables, views, triggers, stored procedures, and more.
Solution
SQL Server has the OBJECTPROPERTY() system function that allows you to return information about database objects such as tables, views, triggers, etc.
The basic syntax for this function is:
OBJECTPROPERTY ( id , property )
- Id is the id of the object.
- Property is the name of the property to display the related information.
This Microsoft article lists all the properties that can be returned using the OBJECTPROPERTY() function, so refer to that link for the full list.
Using OBJECTPROPERTY() to Get Type of Object
For a better understanding, this section will provide a few use cases for this function to help explain how to use it to address your needs.
Let's check out the first example where you want to find whether a specified object is a table, view, trigger, or any other object type.
To determine if the specified object is a table, view, or other object, we will use the following properties for this function:
- IsTable tells whether the specified object is a table or not: 1 = Table; 0 = No Table.
- IsUserTable tells whether the specified table is a user table or not: 1 = User Table; 0 = No User Table.
- IsView tells whether the specified object is a view or not: 1 = View; 0 = No View.
- IsTrigger tells whether a specified object is a trigger or not: 1 = Trigger; 0 = No Trigger.
- IsProcedure tells whether a specified object is a procedure or not: 1 = Procedure; 0 = No Procedure.
I executed the query below where I specified an object using the OBJECT_ID function. That function takes the object name and returns the ID which is what is needed for the OBJECTPROPERTY function.
SELECT OBJECTPROPERTY(Object_ID('Sales'), 'IsTable') AS [Table],
OBJECTPROPERTY(Object_ID('Sales'), 'IsUserTable') AS [UserTable],
OBJECTPROPERTY(Object_ID('Sales'), 'IsView') AS [View],
OBJECTPROPERTY(Object_ID('Sales'), 'IsTrigger') AS [Trigger]
The image below shows the results from the query. Here we can see that two properties (IsTable and IsUserTable) have returned values of 1, while the others returned 0. This means the specified object (Object_ID) is a Table and a User Table.
Using OBJECTPROPERTY() to Get Table Index Information
Let's check out another example. These properties will explain indexing information for a specified table:
- IsIndexed tells whether the specified table has an index or not: 1 = Index; 0 = No Index.
- IsIndexable tells whether an index can be created on the specified table or not: 1 = Indexable; 0 = Not Indexable.
- TableHasClustIndex tells whether the specified table has a clustered index or not: 1 = Cluster; 0 = No Cluster.
- TableHasNonclustIndex tells whether the specified table has a nonclustered index or not. 1 = Nonclustered; 0 = No Nonclustered.
Below is the query to get index-related details for user table Sales.
SELECT OBJECTPROPERTY(Object_ID('Sales'), 'IsIndexed') AS [Indexed Table],
OBJECTPROPERTY(Object_ID('Sales'), 'IsIndexable') AS [Indexable Table],
OBJECTPROPERTY(Object_ID('Sales'), 'TableHasClustIndex') AS [TableHasClustIndex],
OBJECTPROPERTY(Object_ID('Sales'), 'TableHasNonclustIndex') AS [TableHasNonclustIndex]
The output below shows that the Sales table has no indexes, but it is indexable.
Let's create a nonclustered index on this table and rerun the above query.
USE [TESTDB]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [NonClusteredIndexSales] ON [dbo].[Sales]
([ProductName] ASC
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
GO
The image below shows that the index was created successfully.
The output now shows that table Sales is an indexed table with a nonclustered index.
Using OBJECTPROPERTY() to Get Function Information
We can look at another use case for function-related information. I have used the two properties below to see if a specified function is a deterministic function and a table-valued function or not.
- IsDeterministic tells if a function is deterministic or not: 1 = Deterministric; 0 = Not Deterministic.
- IsTableFunction tells is a function is table-valued or not: 1 = Table Valued; 0 = Not Table Valued.
Here is the query where I specified a function along with the above two properties to return its result.
SELECT OBJECTPROPERTY(Object_ID('[dbo].[fn_returnempsales]'), 'IsDeterministic') AS [IsDeterministic],
OBJECTPROPERTY(Object_ID('[dbo].[fn_returnempsales]'), 'IsTableFunction') AS [IsTableFunction]
The output shows that the specified function is table-valued and is not deterministic.
Using OBJECTPROPERTY() to Filter Object List
We can also use this system function in the WHERE clause to filter the result based on the specified property. In the code below I am returning all user tables, the type and create date from sys.objects for the dbo schema. I have used the system function OBJECTPROPERTY() in the WHERE clause to filter the specific schema and object type as a user table in the query below.
SELECT name, type_desc, create_date FROM sys.objects WHERE OBJECTPROPERTY(object_id, N'SchemaId') = SCHEMA_ID(N'dbo') AND type_desc ='USER_TABLE' GO
The output shows all user tables that belong to schema dbo.
Next Steps
- Check out these related articles
About the author
Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-09-30
