views:

310

answers:

1

Hi guys,

Would any of you know how to get the list of computed columns in a SQL Server database table?

I found sys.sp_help tablename does return this information, but only in the secord resultset.

I am trying to find out if there is a better way of doing this. Something which only returns a single result set.

Any help is very appreciated, as this is very badly documented.

Thank you, Giammarco

+6  A: 

Sure - check the sys.columns system catalog view:

SELECT * FROM sys.columns
WHERE is_computed = 1

This gives you all computed columns in this database.

If you want those for just a single table, use this query:

SELECT * FROM sys.columns
WHERE is_computed = 1
AND object_id = OBJECT_ID('YourTableName')

This works on SQL Server 2005 and up.

UPDATE: There's even a sys.computed_columns system catalog view which also contains the definition (expression) of the computed column - just in case that might be needed some time :-)

SELECT * FROM sys.computed_columns
WHERE object_id = OBJECT_ID('YourTableName')

Marc

marc_s
Hi Mark, this solved the problem. Thank you.
Gia
By the way, this is EXACTLY what I was looking for! :-)
Gia
Hi Mark, thanks again for the help! You have really saved me a lot of time today. Do you do any freelance work? I am currently developing a Windows Forms application for the comparison of database tables. The application is nearly complete, I just need to handle execeptions such as computed columns. If sales will go well in a few months, I might need some help.
Gia
contact me at "csharpdev (at) gmx.net" - thanks!
marc_s
Great, thanks MArc!
Gia