Get List of Computed Columns in Database Table (SQL Server)

Get List of Computed Columns in Database Table (SQL Server)

Check the sys.columns system catalog view:

SELECT *
FROM sys.columns
WHERE is_computed = 1

This gives you all computed columns in this database.

If you want those for just a single table, use this query:

SELECT *
FROM sys.columns
WHERE is_computed = 1
AND object_id = OBJECT_ID('YourTableName')

This works on SQL Server 2005 and up.

UPDATE: There's even a sys.computed_columns system catalog view which also contains the definition (expression) of the computed column - just in case that might be needed some time.

SELECT *
FROM sys.computed_columns
WHERE object_id = OBJECT_ID('YourTableName')

How to find Computed field in a table

Assuming you require that information for all colums, you can obtain it using sys.columns e.g.

select C.name, c.is_computed -- try c.* to see all info
from sys.columns c
where c.[object_id] = OBJECT_ID('table1')

If you only need that information for a single column you can obtain it using COLUMNPROPERTY e.g.

SELECT COLUMNPROPERTY(OBJECT_ID('table1'),'field1','IsComputed')

SQL Server: Computed Column defintions retrievable from Database?

This works in SQL Server 2008

create table dbo.Foo
(
StartDate int,
CallDT AS (CONVERT([datetime],dateadd(second,[StartDate],'01/01/1970'),(0)))
)

select definition 
from sys.computed_columns 
where name='CallDT' and object_id=object_id('dbo.Foo')

Finding computed fields in Firebird 2.5

If you wanted computed fields instead - that is what you have to query for, the expression used to compute.

RDB$FIELDS stores definitions of columns and domains, both system and custom. This is where the detailed data attributes are stored for all columns.

The column RDB$FIELDS.RDB$FIELD_NAME links to RDB$RELATION_FIELDS.RDB$FIELD_SOURCE

RDB$COMPUTED_BLR - The binary language representation (BLR) of the SQL expression the database server uses for evaluation when accessing a COMPUTED BY column

https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref-appx04-fields

Hence, something like this should work:

select r.rdb$field_name --, r.rdb$update_flag
from rdb$relation_fields r, RDB$FIELDS f
where r.rdb$relation_name = 'specific_table'
  and f.RDB$FIELD_NAME = r.RDB$FIELD_SOURCE
  and f.RDB$COMPUTED_BLR is not NULL

P.S. https://github.com/FirebirdSQL/firebird-documentation/issues/157

Create Computed Column using data from another table

You can create a user-defined function for that:

CREATE FUNCTION dbo.CountUses(@pictureId INT)
RETURNS INT
AS
  BEGIN
      RETURN
        (SELECT Count(id)
         FROM   PictureUse
         WHERE  PictureId = @PictureId)
  END 

The computed column can then be added like this:

ALTER TABLE dbo.Picture
ADD NofUses AS dbo.CountUses(Id)

However, I would rather make a view for this:

CREATE VIEW PictureView
AS
  SELECT Picture.Id,
         PictureName,
         Picture.CreateDate,
         Count(PictureUse.Id) NofUses
  FROM   Picture
         JOIN PictureUse
           ON Picture.Id = PictureUse.PictureId
  GROUP  BY Picture.Id,
            PictureName,
            Picture.CreateDate 

SQL Server computed column select from another table

You won't be able to use columns from another table within a computed column expression. This is an extract from the MSDN documentation.

A computed column is computed from an expression that can use other columns in the same table.

You mentioned that your motivation for using a computed column was to increase performance. There are a lot of restrictions but an indexed view might add value here.

---

Related Topics