How to Find Definition of Computed Column in SQL Server using T-SQL? – Interview Question of the Week #233

July 14, 2019

Question: How to Find Definition of Computed Column in SQL Server using T-SQL?

Answer: A very fair question, I received the other day SQL Server Performance Tuning Practical Workshop. I really love simple questions which require a small amount of research. If a person can’t find out the answer with quick research he/she need a bit more experience.

The simplest possible method to find the definition of Computed column is to make use of SYS.COMPUTED_COLUMNS system Catalog view.

Let us create the following data set

CREATE TABLE SALES_ORDERS
(
SALE_ID INT IDENTITY(1,1),
PRODUCT_ID INT,
QUANTITY INT,
PRICE MONEY,
SALES_DATE DATETIME,
TOTAL_SALE_AMOUNT AS (QUANTITY*PRICE)
);

Note that TOTAL_SALE_AMOUNT is a computed column which gets automatically computed whenever it is referred in a SELECT statement

Now to find out the computed column for a given table, use the following query

SELECT
OBJECT_NAME(OBJECT_ID) AS TABLE_NAME,
NAME AS COLUMN_NAME,
DEFINITION
FROM
SYS.COMPUTED_COLUMNS
WHERE
OBJECT_NAME(OBJECT_ID) ='SALES_ORDERS'

The result is

How to Find Definition of Computed Column in SQL Server using T-SQL? - Interview Question of the Week #233 computed_result

It is the simplest method of finding out the definition of computed columns for a table. Let me know if there is any other simpler method. If there is any other method, I will be happy to learn from you and will publish on this blog with due credit to you.

You can also hire me for the workshop or consulting service. I offer a very popular Comprehensive Database Performance Health Check service. While I help organizations to improve their performance, I personally gain knowledge as well. I have pretty much seen every possible error while working with SQL Server. This workshop is crafted from my many years of experience working with over 400 customers to help them build a robust system that runs extremely fast.

Here are a few additional blog posts which are related to this topic:

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