CODEWITHSUNDEEP
sql-server-2005t-sql
satnhak
satnhak

Reputation: 9861

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

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]?

Upvotes: 8

Views: 10207

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44316

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

Upvotes: 8

Martin Smith
Martin Smith

Reputation: 453192

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.

Upvotes: 15

Related Questions