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 do I discover the types of [Order Id]
and [Payment Date]
?
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
- some examples here.
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')
Advanage with using SQL_VARIANT_PROPERTY is that it can also be used on computed columns and expressions
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With