6

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.

6

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
3

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])
  • 3
    Good answer. Parsing occured to me and this is a good way to go about it. I was looking for a system catalogue, but I see I didn't specify it in the question. If there isn't a better answer, I'll accept it. – Zikato Apr 25 at 12:39
  • @Zikato Thanks! I agree that there should be a better way to get it than parsing, maybe someone else knows a way to get it through a dmv. – Randi Vertongen Apr 25 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.