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 second result-set.
I am trying to find out if there is a better way of doing this. Something which only returns a single result set.
5条答案
按热度按时间bnl4lu3b1#
Check the
sys.columns
system catalog view:This gives you all computed columns in this database.
If you want those for just a single table, use this query:
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.iyfamqjs2#
If you want to use the
INFORMATION_SCHEMA
views, then trydnph8jn43#
For SQL Server 2000 the syntax is:
And the slightly more useful:
sample output:
tvmytwxo4#
If you have many tables with computed columns and want to see the table names as well:
ogsagwnx5#
Building upon Ian Boyd's answer you can also get the computed text (SQL query) and a sample select statement to test the calculated field using this query:
Example: