Kdtyjb

I'm trying to mass retype columns. That means first dropping and recreating any constraints they are part of.

I found columns referenced by these constraints

• Foreign Keys,


• Primary Keys,


• Indexes,


• Check constraints,


• Rules,


• Default constraints.

But I cannot find Computed columns.

I've looked into INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE, but it doesn't include Computed Columns.

There is also sys.computed_columns which shows definition, but doesn't list columns in searchable manner.

Is there anywhere else I can look? If SQL Server can figure out the dependence, I thought I would be able to as well.

Thanks to Scott Hodgin I found it in sys.sql_expression_dependencies

SELECT 

    OBJECT_NAME(sed.referencing_id)     AS referencingTable

    , pc.[name] AS computedColumn

    , pc.is_computed

    , cc.[name] AS referencedcolumn

    , cc.is_computed

FROM sys.sql_expression_dependencies sed

JOIN sys.[columns] pc ON sed.referencing_minor_id = pc.column_id AND sed.referencing_id = pc.[object_id]

JOIN sys.[columns] cc ON sed.referenced_minor_id = cc.column_id AND sed.referenced_id = cc.[object_id]

WHERE sed.referencing_minor_id > 0      -- referencing object is Column

AND sed.referenced_minor_id > 0         -- referenced object is Column

AND sed.referencing_id = sed.referenced_id  -- references the same table

There is also sys.computed_columns which shows definition, but doesn't
list columns in searchable manner.

If I understand correctly you want to find which columns are referenced by the computed column.

One solution would be searching the definition in sys.computed_columns with CHARINDEX() for each column where the object_id matches

SELECT DISTINCT c.name, 

                cc.definition 

FROM sys.columns c

CROSS APPLY

(

SELECT definition from sys.computed_columns cc

WHERE c.object_id = cc.object_id

AND CHARINDEX(c.name,cc.definition) > 0

) as cc;

Quick test

--Create a heap table.

CREATE TABLE dbo.test(id int,

                      val int);

-- add computed column on two columns.

ALTER TABLE dbo.test 

ADD computedcolumn as id + val;



-- add a column that is not part of any computed column.

ALTER TABLE dbo.test 

ADD bla int;

The query for one specific table

SELECT DISTINCT c.name, 

                cc.definition 

FROM sys.columns c

CROSS APPLY

(

SELECT definition from sys.computed_columns cc

WHERE c.object_id = cc.object_id

AND CHARINDEX(c.name,cc.definition) > 0

) as cc

where c.object_id = object_id('dbo.test');

Result

name    definition

id  ([id]+[val])

val ([id]+[val])