如何打印常量的数据类型

Question

Python有一个type函数。是否有SQL函数或其他方法来打印常量的数据类型，如50或'Hello' ?只是把它当作一个学习工具。

Answer 1

返回T-SQL常量的数据类型元数据的一种方法是使用sp_describe_first_result_set。包含整数约束的SELECT查询的示例用法:

EXEC sp_describe_first_result_set N'SELECT 1';

结果:

+-----------+----------------+------+-------------+----------------+------------------+------------+-----------+-------+----------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+--------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+
| is_hidden | column_ordinal | name | is_nullable | system_type_id | system_type_name | max_length | precision | scale | collation_name | user_type_id | user_type_database | user_type_schema | user_type_name | assembly_qualified_type_name | xml_collection_id | xml_collection_database | xml_collection_schema | xml_collection_name | is_xml_document | is_case_sensitive | is_fixed_length_clr_type | source_server | source_database | source_schema | source_table | source_column | is_identity_column | is_part_of_unique_key | is_updateable | is_computed_column | is_sparse_column_set | ordinal_in_order_by_list | order_by_is_descending | order_by_list_length | tds_type_id | tds_length | tds_collation_id | tds_collation_sort_id |
+-----------+----------------+------+-------------+----------------+------------------+------------+-----------+-------+----------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+--------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+
|         0 |              1 | NULL |           0 |             56 | int              |          4 |        10 |     0 | NULL           | NULL         | NULL               | NULL             | NULL           | NULL                         | NULL              | NULL                    | NULL                  | NULL                |               0 |                 0 |                        0 | NULL          | NULL            | NULL          | NULL         | NULL          |                  0 | NULL                  |             0 |                  1 |                    0 | NULL                     | NULL                   | NULL                 |          56 |          4 | NULL             | NULL                  |
+-----------+----------------+------+-------------+----------------+------------------+------------+-----------+-------+----------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+--------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+

编辑:

@Larnu在评论中提到sys.dm_exec_describe_first_result_setTVF提供了类似的功能。对于您的用例，该函数有助于仅从结果中获取类型名称列:

DECLARE @ConstantExpression nvarchar(MAX) = '1';
SELECT system_type_name FROM sys.dm_exec_describe_first_result_set(N'SELECT '+@ConstantExpression,DEFAULT,DEFAULT);

结果:

system_type_name
int

Answer 2

您可以尝试SQL_VARIANT_PROPERTY函数并指定不同的属性名称，例如'basetype'等。像这样

declare @col            varchar(8000)='Hello';
select sql_variant_property(@col, 'basetype') sql_base_type,
sql_variant_property(@col, 'precision') sql_precision,
sql_variant_property(@col, 'scale') sql_scale,
sql_variant_property(@col, 'MaxLength') sql_maxlen;

sql_base_type   sql_precision   sql_scale   sql_maxlen
varchar         0               0           8000