TSQL Force Cast all columns in a view definition

Question

I have about 500 simple Sql Views that each reference 1 or 2 underlying tables. The views are used to interface to another system.

How do I force cast all columns to reflect the current datatypes

I want to do this so that if underlying table columns change, the output of the Views remain unaffected.

SELECT  
    CAST (T1.ColumnA AS NUMERIC(38, 0)),
    CAST (T1.ColumnB AS DATE),
    CAST (T1.ColumnC AS VARCHAR(900)),
    CAST (T2.ColumnA AS TIME(7)),
    CAST (T2.ColumnB AS VARCHAR(4))
FROM 
    Table1 T1   
LEFT JOIN 
    Table2 T2 ON T1.ID = T2.ID

All suggestions welcome.

Answer 1

You do not need to do anything. Even if the table columns types are changed, these in the view(s) are not. If you want to change the columns types in the view, you need to explicitly do this by dropping/recreating the view or using sp_refreshview. So, there is nothing to worry about.

Here is a code, demonstrating this:

DROP TABLE IF EXISTS [dbo].[StackOverflow];

CREATE TABLE [dbo].[StackOverflow]
(
    [ColumnA] INT
   ,[ColumnB] NVARCHAR(25)
   ,[ColumnC] DATETIME2(0)
);

GO

DROP VIEW IF EXISTS [dbo].[vw_StackOverflow];
GO

CREATE VIEW [dbo].[vw_StackOverflow]
AS
SELECT [ColumnA]
      ,[ColumnB]
      ,[ColumnC] 
FROM [dbo].[StackOverflow];

GO

SELECT C.[name]
      ,T.[name]
      ,C.[max_length]
      ,c.[precision]
      ,c.[scale]
FROM [sys].[columns] C
INNER JOIN [sys].[types] T
    ON C.[system_type_id] = T.[system_type_id]
WHERE [object_id] = OBJECT_ID('[dbo].[vw_StackOverflow]');

GO

ALTER TABLE [dbo].[StackOverflow]
ALTER COLUMN [ColumnA] BIGINT;

ALTER TABLE [dbo].[StackOverflow]
ALTER COLUMN [ColumnB] NVARCHAR(12);

ALTER TABLE [dbo].[StackOverflow]
ALTER COLUMN [ColumnC] DATETIME2(7);

GO


SELECT C.[name]
      ,T.[name]
      ,C.[max_length]
      ,c.[precision]
      ,c.[scale]
FROM [sys].[columns] C
INNER JOIN [sys].[types] T
    ON C.[system_type_id] = T.[system_type_id]
WHERE [object_id] = OBJECT_ID('[dbo].[vw_StackOverflow]');


GO

EXEC sp_refreshview N'[dbo].[vw_StackOverflow]';

GO


SELECT C.[name]
      ,T.[name]
      ,C.[max_length]
      ,c.[precision]
      ,c.[scale]
FROM [sys].[columns] C
INNER JOIN [sys].[types] T
    ON C.[system_type_id] = T.[system_type_id]
WHERE [object_id] = OBJECT_ID('[dbo].[vw_StackOverflow]');


GO