computed column references
-
I know how to get a computed column -
"SELECT syscolumns.name FROM syscolumns
JOIN sysobjects ON sysobjects.id = syscolumns.id
AND syscolumns.iscomputed = 1 AND sysobjects.xtype = 'U'"
I'm sure somewhere in the information schema views there should be a way to get the columns used by a computed column....I'm still looking as I'm intrigued by this one - maybe someone else will come up with the answer meanwhile.....!!!
**ASCII stupid question, get a stupid ANSI !!!** -
I can't test from here... reply to this message so I won't forget to answer this one tomorrow.
-
For the definition of the column, you can look in syscomments:
SELECT [text] FROM syscomments WHERE id = OBJECT_ID('TABLENAME') AND number = @columnIdInTable -
Here's the query you requested :
Select
dtCC.Tname as TableName
, dtCC.CName as ColName
, dtCC.XType
, dtCC.IsComputed
, dtCC.Text
, C.Name AS ColUsed
FROM
(
Select
O.Name as TName
, C.name as CName
, O.XType
, C.IsComputed
, CM.Text
, O.id
from dbo.SysColumns C inner join
dbo.SysObjects O on C.id = O.id inner join
dbo.SysComments CM on O.id = CM.id
where C.IsComputed = 1 and O.XType = 'U'
) dtCC Left outer join dbo.SysColumns C ON dtCC.id = C.id and CHARINDEX('[' + C.name + ']', dtCC.Text, 1) > 0
-
Remi - I don't know if you do this already but you should start submitting scripts to scc.com so they can include it in their knowledge base for ready reference!!!
**ASCII stupid question, get a stupid ANSI !!!** -
Think this can be usefull??
-
It was posted here wasn't it ?!
**ASCII stupid question, get a stupid ANSI !!!** -
If you say so
. -
Thanks, that's a good solution. I unfortunately found a bit of a problem with it. It will return a false positive in the situation where a function with the same name as a column in the table is used in the definition of the computed column. For instance:
CREATE FUNCTION ColumnName (@inputint int)
returns int
begin
RETURN @inputint + 3
end
CREATE TABLE TestComputed
(ColumnName int,
AlsoAColumnName int,
ComputedColumn AS DBO.ColumnName(AlsoAColumnName) * 2)
I am however quite confident that the probability of that ever happening is low enough to be ignored. Or am i mistaken?
-
I don't think so because I think that the definition is rewritten to include the column name between brackets so DBO.ColumnName(AlsoAColumnName) would become DBO.ColumnName([AlsoAColumnName]) and the script would pick only the part between the brackets.
Can you prove me wrong?? I don't have access to a server right now.
-
It appears as if the server pops the brackets around the function name as well. The text value in syscomments for the created table that i posted is:
([DBO].[ColumnName]([AlsoAColumnName]) * 2)
Do you think this is something to worry about though? I don't think the situation will ever arise where somebody calls a function with the same name a column in the same table as the computed column. Dunno, might be wrong.
-
Not for me because I always prefix my functions name with fnFunctionName
.It could be a problem. Maybe a check that joins the function names to the column names could send a warning of such a possibility. Other than that there's just no way to be sure. I don't think this script can be totally safe to use without human checks.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply