SQL Server: Get all computed columns with table names

In Programming Tags #sql-server Published 8/24/2015

I just wanted to share a query that will pull in all computed columns/fields from a SQL database and include the table name, the column name, the data type/meta data and the definition of the formula used to compute it.

SQL

SELECT
    OBJECT_NAME(c.object_id) as 'table',
    c.name as 'name',
    t.Name as 'type',
    c.max_length 'max length',
    c.precision,
    c.scale,
    c.is_nullable,
    c.is_computed,
    cc.definition
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
INNER JOIN
    sys.computed_columns cc on cc.object_id = c.object_id and cc.column_id = c.column_id
WHERE
    c.is_computed = 1
ORDER BY 'table', 'name'

Leave a comment

Please note that we won't show your email to others, or use it for sending unwanted emails. We will only use it to render your Gravatar image and to validate you as a real person.