May 25, 2011 at 7:33 am
I'm trying to write a query that will display the formula for a computed column in SQL Server 2008R2.
I have looked here:
http://msdn.microsoft.com/en-us/library/ms177173.aspx
and it say (at least I think) that I can look at the Formula property of COLUMNPROPERTY like this:
SELECT COLUMN_NAME ,
COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'IsComputed'),
COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'Formula'),
COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'IsDeterministic')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Event' AND COLUMN_NAME = 'CurrentAttendance'
I know the column is computed and I can see the formula in SSMS. I wanted to do this in T-SQL.
Does anyone know how to get this value?
Thanks
Gary
May 25, 2011 at 7:53 am
SELECT name,definition
FROM sys.Computed_columns
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 25, 2011 at 7:59 am
Thank you.
May 25, 2011 at 8:14 am
You may want the Table Name as well.
SELECT obj.name AS TableName,col.name AS ColumnName,col.definition
FROM sys.Computed_columns AS col
INNER JOIN sys.objects AS obj ON col.object_id = obj.object_id
ORDER BY obj.name, col.name
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 25, 2011 at 8:31 am
I only showed a peice of what I am doing this is the whole query:
SELECT
COLUMN_NAME AS [Field Name] ,
DATA_TYPE AS [Data Type] ,
CASE DATA_TYPE
WHEN 'char' THEN CASE CHARACTER_MAXIMUM_LENGTH
WHEN -1 THEN 'MAX'
ELSE CONVERT(VARCHAR(1000), CHARACTER_MAXIMUM_LENGTH)
END
WHEN 'nchar'
THEN CASE CHARACTER_MAXIMUM_LENGTH
WHEN -1 THEN 'MAX'
ELSE CONVERT(VARCHAR(1000), CHARACTER_MAXIMUM_LENGTH)
END
WHEN 'varchar'
THEN CASE CHARACTER_MAXIMUM_LENGTH
WHEN -1 THEN 'MAX'
ELSE CONVERT(VARCHAR(1000), CHARACTER_MAXIMUM_LENGTH)
END
WHEN 'nvarchar'
THEN CASE CHARACTER_MAXIMUM_LENGTH
WHEN -1 THEN 'MAX'
ELSE CONVERT(VARCHAR(1000), CHARACTER_MAXIMUM_LENGTH)
END
WHEN 'decimal'
THEN '(' + CONVERT(VARCHAR(10), NUMERIC_PRECISION) + ','
+ CONVERT(VARCHAR(10), NUMERIC_SCALE) + ')'
WHEN 'float'
THEN '(' + CONVERT(VARCHAR(10), NUMERIC_PRECISION) + ','
+ CONVERT(VARCHAR(10), NUMERIC_SCALE) + ')'
END AS [Sizing (Percision,Scale)] ,
CASE INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE
WHEN 'YES' THEN 'Yes'
ELSE 'No'
END AS [Nullable] ,
CASE COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity')
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [Identity] ,
COLUMN_DEFAULT AS [Default Value] ,
CASE COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), column_name, 'IsComputed')
WHEN 1 THEN 'Yes'
ELSE NULL
END AS [Computed Column] ,
ORDINAL_POSITION AS [Position in Table] ,
CC.definition AS [Caclulated Field Formula]
FROM
INFORMATION_SCHEMA.COLUMNS
LEFT OUTER JOIN sys.computed_columns CC
ON INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = CC.name
WHERE
TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'Event'
This is for an app I am working on to display information about a database.
May 25, 2011 at 8:39 am
gary.mazzone (5/25/2011)
I only showed a peice of what I am doing this is the whole query:
Did you have a question?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 25, 2011 at 8:43 am
No that will complete what I want to show for basic column information on the tab. The are other areas that show Parent- child relationships, indexes, views against the table, procs against the tables. Check constraints on the table, permissions and a couple of other things.
It is still a work in progress at this point. It works. I just wanted to let you know that was just an example to find the information not the whole query I am using
June 12, 2014 at 11:19 am
Nice work SSCommitted. Do you have it completed with indexes and stuff? Thanks.
September 23, 2015 at 1:56 am
Thanks a lot!
You need to add a schema name also in case of schema not equal to 'dbo'
COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),COLUMN_NAME,'IsComputed')
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply