2

I want to search whether a columns is participating in a computed column expression.

I came up with this 

SELECT 
    (select name 
     from sys.columns 
     where column_id = sys.sql_expression_dependencies.referencing_minor_id 
     and object_id = sys.sql_expression_dependencies.referencing_id) as [dependant_column]
FROM sys.sql_expression_dependencies
WHERE referencing_minor_id > 0 
    and referencing_class = 1 
    and referenced_class = 1 
    and object_name(referencing_id) = 'trns1'
    and referenced_minor_id = (select column_id 
                           from sys.columns 
                           where name = 'class1' 
                           and OBJECT_NAME(object_id) = 'trns1')

Please help.

tom redfern
  • 30,562
  • 14
  • 91
  • 126
Soham Dasgupta
  • 5,061
  • 24
  • 79
  • 125

1 Answers1

3

You want to look at the sys.computed_columns table:

select name, definition 
from sys.computed_columns

This is a very similar question: SQL Server: Computed Column defintions retrievable from Database?

There are probably some more kicking around.

Community
  • 1
  • 1
James Osborn
  • 1,275
  • 7
  • 12