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
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)