How to find if a column is referenced in a computed column? Unicorn Meta Zoo #1: Why another podcast? Announcing the arrival of Valued Associate #679: Cesar ManaraWhy does OBJECT_ID return NULL in a computed column?Deadlocks on primary key row locks race condition with temporal tablesMulti-schema, multi-tenant databases - data isolation through composite primary keyshow to find the database context of the last sql statement?How to find Stored Procedures With Recompile?Validate data between two large tablesHow to create a Hash Computed Column for Many Columns?HashBytes Computed Column: Exclude ColumnSQL Server: Covering indexes including all columns?Mimicking cascading foreign key referencing system table
Why didn't the Space Shuttle bounce back into space as many times as possible so as to lose a lot of kinetic energy up there?
"My boss was furious with me and I have been fired" vs. "My boss was furious with me and I was fired"
Can a level 2 Warlock take one level in rogue, then continue advancing as a warlock?
Is there any pythonic way to find average of specific tuple elements in array?
My bank got bought out, am I now going to have to start filing tax returns in a different state?
What was Apollo 13's "Little Jolt" after MECO?
Unable to completely uninstall Zoom meeting app
Co-worker works way more than he should
Did the Roman Empire have penal colonies?
Why do games have consumables?
Can you stand up from being prone using Skirmisher outside of your turn?
What makes accurate emulation of old systems a difficult task?
What is the term for a person whose job is to place products on shelves in stores?
All ASCII characters with a given bit count
Retract an already submitted recommendation letter (written for an undergrad student)
What is the best way to deal with NPC-NPC combat?
A strange hotel
A Paper Record is What I Hamper
How exactly does Hawking radiation decrease the mass of black holes?
What's the difference between using dependency injection with a container and using a service locator?
How to keep bees out of canned beverages?
finding a tangent line to a parabola
Can I criticise the more senior developers around me for not writing clean code?
Scheduling based problem
How to find if a column is referenced in a computed column?
Unicorn Meta Zoo #1: Why another podcast?
Announcing the arrival of Valued Associate #679: Cesar ManaraWhy does OBJECT_ID return NULL in a computed column?Deadlocks on primary key row locks race condition with temporal tablesMulti-schema, multi-tenant databases - data isolation through composite primary keyshow to find the database context of the last sql statement?How to find Stored Procedures With Recompile?Validate data between two large tablesHow to create a Hash Computed Column for Many Columns?HashBytes Computed Column: Exclude ColumnSQL Server: Covering indexes including all columns?Mimicking cascading foreign key referencing system table
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
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.
t-sql sql-server-2016 dmv system-tables
add a comment |
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.
t-sql sql-server-2016 dmv system-tables
1
Did you look into sys.dm_sql_referenced_entities?
– Scott Hodgin
1 hour ago
Yes, that's not it. But you pointed me in the right direction and I found it in sys.sql_expression_dependencies.
– Zikato
1 hour ago
add a comment |
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.
t-sql sql-server-2016 dmv system-tables
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.
t-sql sql-server-2016 dmv system-tables
t-sql sql-server-2016 dmv system-tables
asked 4 hours ago
ZikatoZikato
23918
23918
1
Did you look into sys.dm_sql_referenced_entities?
– Scott Hodgin
1 hour ago
Yes, that's not it. But you pointed me in the right direction and I found it in sys.sql_expression_dependencies.
– Zikato
1 hour ago
add a comment |
1
Did you look into sys.dm_sql_referenced_entities?
– Scott Hodgin
1 hour ago
Yes, that's not it. But you pointed me in the right direction and I found it in sys.sql_expression_dependencies.
– Zikato
1 hour ago
1
1
Did you look into sys.dm_sql_referenced_entities?
– Scott Hodgin
1 hour ago
Did you look into sys.dm_sql_referenced_entities?
– Scott Hodgin
1 hour ago
Yes, that's not it. But you pointed me in the right direction and I found it in sys.sql_expression_dependencies.
– Zikato
1 hour ago
Yes, that's not it. But you pointed me in the right direction and I found it in sys.sql_expression_dependencies.
– Zikato
1 hour ago
add a comment |
2 Answers
2
active
oldest
votes
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
add a comment |
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])
2
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
2 hours ago
@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
2 hours ago
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f236660%2fhow-to-find-if-a-column-is-referenced-in-a-computed-column%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
add a comment |
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
add a comment |
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
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
answered 1 hour ago
ZikatoZikato
23918
23918
add a comment |
add a comment |
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])
2
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
2 hours ago
@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
2 hours ago
add a comment |
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])
2
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
2 hours ago
@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
2 hours ago
add a comment |
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])
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])
edited 3 hours ago
answered 3 hours ago
Randi VertongenRandi Vertongen
5,2911926
5,2911926
2
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
2 hours ago
@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
2 hours ago
add a comment |
2
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
2 hours ago
@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
2 hours ago
2
2
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
2 hours ago
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
2 hours ago
@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
2 hours ago
@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
2 hours ago
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f236660%2fhow-to-find-if-a-column-is-referenced-in-a-computed-column%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Did you look into sys.dm_sql_referenced_entities?
– Scott Hodgin
1 hour ago
Yes, that's not it. But you pointed me in the right direction and I found it in sys.sql_expression_dependencies.
– Zikato
1 hour ago