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

Question: How do you find a computed column’s definition using T-SQL? Read its expression from sys.computed_columns.definition.

This came up in my performance tuning workshop. I enjoy questions that lead to a small, useful piece of research. You don’t need to open every table in the designer to find the formula.

-- Run setup only in your scratch database.
IF OBJECT_ID(N'dbo.SALES_ORDERS', N'U') IS NOT NULL
    THROW 50001, 'The demonstration table already exists.', 1;
CREATE TABLE dbo.SALES_ORDERS
(
    SALE_ID int IDENTITY(1,1), PRODUCT_ID int,
    QUANTITY int, PRICE money, SALES_DATE datetime,
    TOTAL_SALE_AMOUNT AS (QUANTITY * PRICE)
);
SELECT OBJECT_NAME(object_id) AS TABLE_NAME,
       name AS COLUMN_NAME, definition AS DEFINITION
FROM sys.computed_columns
WHERE object_id = OBJECT_ID(N'dbo.SALES_ORDERS', N'U');

The original table calculates TOTAL_SALE_AMOUNT from QUANTITY and PRICE. This example is not PERSISTED. A persisted computed column stores its calculated value; inspect is_persisted in the same catalog view if that distinction matters.

The revised lookup uses the schema-qualified object ID. Filtering only by OBJECT_NAME can mix tables with the same name in different schemas. Run it in the table’s database. No rows can mean the name is wrong, the table has no computed columns, or your account cannot see its metadata.

For an existing table, run only the SELECT and change dbo.SALES_ORDERS to its name. The setup guard deliberately refuses to overwrite a table. If you use another useful catalog shortcut, share it and I will credit the suggestion.

• Computed columns with CASE
• Changing a computed column
• Data type and computed column puzzle
• Historical columnstore limitation

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.