Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
 Posted - 2006-02-16 : 21:28:27
|
| Hi all, We have an update script that runs over our dev databases and updates the tables etc if there is a design change. I can do the comparison using the INFORMATION_SCHEMA.COLUMNS view, but I can't see the computed column expression. I had thought that the expression was being stored in the syscomments table but that seems to be unreliable. My question is: given a table and column name, how can I determine a) if it is a computed column and b) what the expression is?Cheers,Tim |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-16 : 21:37:26
|
| check for syscolumns.iscomputed = 1expression is in syscomments.text----------------------------------'KH'Time is always against us |
 |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-02-16 : 22:05:09
|
| OK - I didn't know about the iscomputed column, but the syscomments.text is not reliable.I just changed the computed column expression and syscomments.text was not updated.Also, I can't see how you would link the syscomments row to the syscolumns row because all they are linked on is the table id. I had thought it was linked also on colid, but this does not seem to be the case. Thanks,Tim |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-16 : 22:52:16
|
| [code]select object_name(c.id) as table_name, c.name as column_name, textfrom syscolumns c inner join syscomments m on c.id = m.id and c.colid = m.numberwhere c.id = object_id('tbl2')[/code]----------------------------------'KH'Time is always against us |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-02-16 : 23:08:42
|
| Thanks - I was thinking this method was unreliable because changes made to a computed column were not showing up in syscomments until I closed and re-opened my database connection......Then I tried running a similar query and got this error message:ODBC: Msg 0, Level 19, State 1SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.So I think I'll give up on this line of enquiry because it looks a bit dangerous...Cheers,Tim |
|
|
|
|
|
|
|
|
How to find Computed columns in system tables?