What is the SQL Server equivalent of pg_typeof(), how can I get the type of a column returned in a result set?
There are various indirect methods, but nothing quite as simple as pg_typeof.
SQL_VARIANT_PROPERTY is often used for convenience, but has limitations since it cannot contain all other types.
Writing the subject of inquiry to a table then inspecting metadata is one method. Convenience system stored procedures like sys.sp_help can be employed as a somewhat more convenient method than writing a query against the metadata views.
Another, conceptually-related idea is to call:

sys.sp_describe_first_result_set (procedure); or
sys.dm_exec_describe_first_result_set (function)

For example:
EXECUTE sys.sp_describe_first_result_set
    @tsql = N'SELECT 1.0;'

SELECT FRS.* 
FROM sys.dm_exec_describe_first_result_set
    (N'SELECT 1.0;', DEFAULT, DEFAULT) AS FRS;

These return a great deal of internal information, including type details. The above example returns:




system_type_name




numeric(2,1)




An alias type example:
CREATE TYPE Banana FROM binary(456) NOT NULL;

EXECUTE sys.sp_describe_first_result_set
    @tsql = N'DECLARE @b Banana; SELECT @b;'

SELECT 
    FRS.system_type_id,
    FRS.system_type_name,
    FRS.user_type_id,
    FRS.user_type_schema,
    FRS.user_type_name
FROM sys.dm_exec_describe_first_result_set
    (N'DECLARE @b Banana; SELECT @b;', DEFAULT, DEFAULT) AS FRS;

returns (in part):




system_type_id
system_type_name
user_type_id
user_type_schema
user_type_name




173
binary(456)
260
dbo
Banana