In SQL Server, there are a few ways to get metadata of the result set from a query. This includes the data type of the columns returned by a T-SQL query.

In particular, the sys.dm_exec_describe_first_result_set system dynamic management function is a good choice for such a task.

Example

Here’s an example to demonstrate how to use sys.dm_exec_describe_first_result_set to get data type information about each column returned by a given T-SQL query.

SELECT 
    name,
    user_type_name,
    system_type_name,
    max_length,
    [precision],
    scale
FROM sys.dm_exec_describe_first_result_set(
    'select * from Clients', 
    null, 
    0);

Result:

+------------+------------------+--------------------+--------------+-------------+---------+
| name       | user_type_name   | system_type_name   | max_length   | precision   | scale   |
|------------+------------------+--------------------+--------------+-------------+---------|
| ClientCode | clientcode       | varchar(8)         | 8            | 0           | 0       |
| FirstName  | NULL             | varchar(60)        | 60           | 0           | 0       |
| LastName   | NULL             | varchar(60)        | 60           | 0           | 0       |
+------------+------------------+--------------------+--------------+-------------+---------+

In this case, three rows are returned, each of which represent a column that would be returned by the query that I am analysing.

You might have noticed that one of the columns uses a user-defined data type alias called clientcode. When you create a user-defined data type alias, you base it on an existing system type. This is reflected in the above result. We can see that clientcode is based on varchar(8).

The sys.dm_exec_describe_first_result_set function returns a lot of columns, so feel free to include all columns to see if there are any others you might find useful. Also see How sys.dm_exec_describe_first_result_set Works for a more detailed explanation and more examples.

You can also use the sp_describe_first_result_set system stored procedure to return the same information (it uses the same algorithm as sys.dm_exec_describe_first_result_set).

Stored Procedure Result Sets

If you want to get the data type of the columns returned by a stored procedure, you can use the sys.dm_exec_describe_first_result_set_for_object function.

This function uses the same algorithm as the previous one, but the difference is that this one accepts the ID of a stored procedure or trigger as its first argument (instead of the actual T-SQL batch).

Therefore, we can use it like this:

SELECT 
    name,
    user_type_name,
    system_type_name,
    max_length,
    [precision],
    scale
FROM sys.dm_exec_describe_first_result_set_for_object(
    OBJECT_ID('sp_BadDogs'),
    0);

Result:

+---------+------------------+--------------------+--------------+-------------+---------+
| name    | user_type_name   | system_type_name   | max_length   | precision   | scale   |
|---------+------------------+--------------------+--------------+-------------+---------|
| DogId   | NULL             | int                | 4            | 10          | 0       |
| DogName | NULL             | nvarchar(255)      | 510          | 0           | 0       |
| GoodDog | NULL             | bit                | 1            | 1           | 0       |
+---------+------------------+--------------------+--------------+-------------+---------+

In this case, I used the OBJECT_ID() function to return the ID of the stored procedure, which saved me from having to know the actual ID.

See How sys.dm_exec_describe_first_result_set_for_object Works for more information and examples for this function.