How to print the data type of a constant-CodePudding

Python has a type function. Is there a SQL function or other method to print the data type of a constant such as 50 or 'Hello' ? Just using this as a learning tool.

CodePudding user response：

One way to return data type meta data of a T-SQL constant is with sp_describe_first_result_set. Example usage with a SELECT query containing an integer constaint:

EXEC sp_describe_first_result_set N'SELECT 1';

Result:

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

EDIT:

@Larnu mentioned in comments that the sys.dm_exec_describe_first_result_set TVF provides similar functionality. For your use case, that function facilitates getting only the type name column from the result:

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

Result:

system_type_name
int

CodePudding user response：

You could try the SQL_VARIANT_PROPERTY function and specify different property names, for example 'basetype' etc. Something like this

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