How to find derived column types of a view in SQL Server 2005?

Question

For example, suppose I have a view:

CREATE VIEW [dbo].[PaymentSchedule_vw] AS SELECT [Order].Id [Order Id] ,[PaymentSchedule].Date [Payment Date] FROM _Order123 [Order] INNER JOIN _Order123_Shedule [PaymentSchedule] ON [Order].Id = [PaymentSchedule].OrderId GO 

How to find out the types of [Order Id] and [Payment Date] ?

Answer 1

 SELECT c.name, t.name, c.max_length, c.precision, c.scale FROM sys.columns c JOIN sys.types t ON t.user_type_id = c.user_type_id AND t.system_type_id = c.system_type_id WHERE object_id = OBJECT_ID('PaymentSchedule_vw') 

Or you can use SQL_VARIANT_PROPERTY - a few examples here .

Answer 2

 SELECT top 1 SQL_VARIANT_PROPERTY([Order Id], 'BaseType') OrderIdBaseType, SQL_VARIANT_PROPERTY([Payment Date], 'BaseType') PaymentDateBaseType FROM [PaymentSchedule_vw] 

You should check the following properties:

 SQL_VARIANT_PROPERTY(<column>, 'BaseType'), SQL_VARIANT_PROPERTY(<column>, 'Precision'), SQL_VARIANT_PROPERTY(<column>, 'Scale'), SQL_VARIANT_PROPERTY(<column>, 'MaxLength') 

A provision using SQL_VARIANT_PROPERTY is that it can also be used for calculated columns and expressions.