SQL Server – How to Find if a Column is Referenced in a Computed Column?

dmvsql serversql-server-2016system-tablest-sql

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.

Best Answer

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

Related Solutions

Sql-server – Moving tables to another SQL2008 database (including indexes, triggers, etc.)

Scripting out the tables, then using SSIS to transfer the data would be the most reliable and effective way of moving the data to the new database.

Sql-server – Why does OBJECT_ID return NULL in a computed column

A metadata function mentioned in a computed column in a #temp table is going to reference tempdb.sys.objects, not yourdatabase.sys.objects, since the table is created in tempdb. Try adding the database prefix to the insert, and that will make OBJECT_ID go find it in the right place:

CREATE TABLE #procs (
  name SYSNAME,
  [object_id] AS OBJECT_ID(name)
);

INSERT INTO #procs (name) VALUES ('dbo.sp_send_dbmail');
INSERT INTO #procs (name) VALUES ('msdb.dbo.sp_send_dbmail');

SELECT name, [object_id] FROM #procs;

Results:

dbo.sp_send_dbmail          NULL
msdb.dbo.sp_send_dbmail     283148054

Best Answer

Related Solutions

Sql-server – Moving tables to another SQL2008 database (including indexes, triggers, etc.)

Sql-server – Why does OBJECT_ID return NULL in a computed column

Related Question