SQL SERVER – Table or View – OBJECTPROPERTY

When I am not writing the blog posts, I spend most of the time helping clients with Comprehensive Database Performance Health Check. One of the epic challenges, I have when I am working with my client is that quite often I am not able to figure out if the object, I am working with is a table or view. Let us learn how I use a quick trick to know the object property with the keyword OBJECTPROPERTY.

Let us assume that I have a table called Sales.Invoices from database WideWorldImporters. Let us use the function OBJECTPROPERTY to figure out if it is a view or table.

SELECT OBJECTPROPERTY(OBJECT_ID(N'Sales.Invoices'), 'IsTable') IsTable,
OBJECTPROPERTY(OBJECT_ID(N'Sales.Invoices'), 'IsView') IsView

When you run the script above you will get the following result.

As you can see, I can easily figure out the object property with the function. Personally, I use this function for quite a lot of things during my consultancy engagement. Let me know if you are interested to know more about this topic and I will write more blogs as well as create an SQL in Sixty Seconds video.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Server, SQL Table Operation, System Object
Previous Post
SQL SERVER – Maximum Column Per Index – 32
Next Post
SQL SERVER – Adaptive Threshold Rows

Related Posts

1 Comment. Leave new

  • tomwickerath
    March 24, 2021 7:23 am

    I like this trick! It’s too bad that people don’t learn to use simple prefix naming conventions, such as “vw_” for views, or “usp_” for user stored procedures. I came from working with Microsoft Access, where it is very common to adopt naming conventions, at least for people who are not absolute newbies.

    Reply

Leave a Reply

Menu
Exit mobile version