In SQL Server we can run a query against the sys.computed_columns system catalog view to return all computed columns and their definitions.
Examples
These examples use the WideWorldImporters sample database:
SELECT
OBJECT_NAME(object_id) AS TableName,
name AS ColumnName,
definition AS ColumnDefinition
FROM sys.computed_columns
ORDER BY TableName, ColumnName;Example result:
TableName ColumnName ColumnDefinition -------------------- --------------------- ------------------------------------------------------------------------------------------ CustomerTransactions IsFinalized (case when [FinalizationDate] IS NULL then CONVERT([bit],(0)) else CONVERT([bit],(1)) end) Invoices ConfirmedDeliveryTime (TRY_CONVERT([datetime2](7),json_value([ReturnedDeliveryData],N'$.DeliveredWhen'),(126))) Invoices ConfirmedReceivedBy (json_value([ReturnedDeliveryData],N'$.ReceivedBy')) People OtherLanguages (json_query([CustomFields],N'$.OtherLanguages')) People SearchName (concat([PreferredName],N' ',[FullName])) StockItems SearchDetails (concat([StockItemName],N' ',[MarketingComments])) StockItems Tags (json_query([CustomFields],N'$.Tags')) SupplierTransactions IsFinalized (case when [FinalizationDate] IS NULL then CONVERT([bit],(0)) else CONVERT([bit],(1)) end)
We can expand it to include information about data type:
SELECT
OBJECT_NAME(object_id) AS TableName,
name AS ColumnName,
definition AS ColumnDefinition,
TYPE_NAME(user_type_id),
max_length,
[precision],
scale
FROM sys.computed_columns
ORDER BY TableName, ColumnName;Example result:
TableName ColumnName ColumnDefinition max_length precision scale -------------------- --------------------- ------------------------------------------------------------------------------------------ --------- ---------- --------- ----- CustomerTransactions IsFinalized (case when [FinalizationDate] IS NULL then CONVERT([bit],(0)) else CONVERT([bit],(1)) end) bit 1 1 0 Invoices ConfirmedDeliveryTime (TRY_CONVERT([datetime2](7),json_value([ReturnedDeliveryData],N'$.DeliveredWhen'),(126))) datetime2 8 27 7 Invoices ConfirmedReceivedBy (json_value([ReturnedDeliveryData],N'$.ReceivedBy')) nvarchar 8000 0 0 People OtherLanguages (json_query([CustomFields],N'$.OtherLanguages')) nvarchar -1 0 0 People SearchName (concat([PreferredName],N' ',[FullName])) nvarchar 202 0 0 StockItems SearchDetails (concat([StockItemName],N' ',[MarketingComments])) nvarchar -1 0 0 StockItems Tags (json_query([CustomFields],N'$.Tags')) nvarchar -1 0 0 SupplierTransactions IsFinalized (case when [FinalizationDate] IS NULL then CONVERT([bit],(0)) else CONVERT([bit],(1)) end) bit 1 1 0
And we can join the sys.objects view to return the schema:
SELECT
SCHEMA_NAME(o.schema_id) AS SchemaName,
OBJECT_NAME(cc.object_id) AS TableName,
cc.name AS ColumnName,
cc.definition AS ColumnDefinition,
TYPE_NAME(cc.user_type_id) AS DataType,
cc.max_length,
cc.[precision],
cc.scale
FROM sys.computed_columns cc
JOIN sys.objects o
ON o.object_id = cc.object_id
ORDER BY SchemaName, TableName, ColumnName;Example result:
SchemaName TableName ColumnName ColumnDefinition DataType max_length precision scale ----------- -------------------- --------------------- ------------------------------------------------------------------------------------------ --------- ---------- --------- ----- Application People OtherLanguages (json_query([CustomFields],N'$.OtherLanguages')) nvarchar -1 0 0 Application People SearchName (concat([PreferredName],N' ',[FullName])) nvarchar 202 0 0 Purchasing SupplierTransactions IsFinalized (case when [FinalizationDate] IS NULL then CONVERT([bit],(0)) else CONVERT([bit],(1)) end) bit 1 1 0 Sales CustomerTransactions IsFinalized (case when [FinalizationDate] IS NULL then CONVERT([bit],(0)) else CONVERT([bit],(1)) end) bit 1 1 0 Sales Invoices ConfirmedDeliveryTime (TRY_CONVERT([datetime2](7),json_value([ReturnedDeliveryData],N'$.DeliveredWhen'),(126))) datetime2 8 27 7 Sales Invoices ConfirmedReceivedBy (json_value([ReturnedDeliveryData],N'$.ReceivedBy')) nvarchar 8000 0 0 Warehouse StockItems SearchDetails (concat([StockItemName],N' ',[MarketingComments])) nvarchar -1 0 0 Warehouse StockItems Tags (json_query([CustomFields],N'$.Tags')) nvarchar -1 0 0