Altering table types, part 2

Altering table types - a compatibility guide

In yesterday's post, I altered a table type. Pray I don't alter them further. What else is incompatible with an integer column? It's just a morbid curiosity at this point as I don't recall having ever seen this after working with SQL Server for 18 years. Side note, dang I'm old

How best to answer the question, by interrogating the sys.types table and throwing operations against the wall to see what does/doesn't stick.

DECLARE
    @Results table
(
    TypeName sysname, Failed bit, ErrorMessage nvarchar(4000)
);

DECLARE
    @DoOver nvarchar(4000) = N'DROP TABLE IF EXISTS dbo.IntToTime;
CREATE TABLE dbo.IntToTime (CREATE_TIME int);'
,   @alter nvarchar(4000) = N'ALTER TABLE dbo.IntToTime ALTER COLUMN CREATE_TIME @type'
,   @query nvarchar(4000) = NULL
,   @typeName sysname = 'datetime';

DECLARE
    CSR CURSOR
FORWARD_ONLY
FOR
SELECT 
    T.name
FROM
    sys.types AS T
WHERE
    T.is_user_defined = 0

OPEN CSR;
FETCH NEXT FROM CSR INTO @typeName
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY   
        EXECUTE sys.sp_executesql @DoOver, N'';
        SELECT @query = REPLACE(@alter, N'@type', @typeName);
        EXECUTE sys.sp_executesql @query, N'';
        
        INSERT INTO
            @Results
        (
            TypeName
        ,   Failed
        ,   ErrorMessage
        )
        SELECT @typeName, CAST(0 AS bit), ERROR_MESSAGE();
    END TRY
    BEGIN CATCH
        INSERT INTO
            @Results
        (
            TypeName
        ,   Failed
        ,   ErrorMessage
        )
        SELECT @typeName, CAST(1 AS bit), ERROR_MESSAGE()
    END CATCH
    FETCH NEXT FROM CSR INTO @typeName
END
CLOSE CSR;
DEALLOCATE CSR;

SELECT
*
FROM
    @Results AS R
ORDER BY
    2,1;

TypeName	Failed	ErrorMessage
bigint	0
binary	0
bit	0
char	0
datetime	0
decimal	0
float	0
int	0
money	0
nchar	0
numeric	0
nvarchar	0
real	0
smalldatetime	0
smallint	0
smallmoney	0
sql_variant	0
sysname	0
tinyint	0
varbinary	0
varchar	0
date	1	Operand type clash: int is incompatible with date
datetime2	1	Operand type clash: int is incompatible with datetime2
datetimeoffset	1	Operand type clash: int is incompatible with datetimeoffset
geography	1	Operand type clash: int is incompatible with geography
geometry	1	Operand type clash: int is incompatible with geometry
hierarchyid	1	Operand type clash: int is incompatible with hierarchyid
image	1	Operand type clash: int is incompatible with image
ntext	1	Operand type clash: int is incompatible with ntext
text	1	Operand type clash: int is incompatible with text
time	1	Operand type clash: int is incompatible with time
timestamp	1	Cannot alter column 'CREATE_TIME' to be data type timestamp.
uniqueidentifier	1	Operand type clash: int is incompatible with uniqueidentifier
xml	1	Operand type clash: int is incompatible with xml