0

I have this SQL query that I am running on a .NET application, and I want to determine the types of each of the column of the resulting query. The query has multiple joins, column aliasing and possible casting as well. For example I could have something like this,

SELECT
     T1.col1,
     T1.col2,
     T2.col1 AS alias1,
     CAST(T2.col2 AS varchar) AS cast1,
     CAST(T3.col1 AS nvarchar) AS cast2,
     T3.col2 AS alias2
FROM table1 T1
     LEFT JOIN table2 T2
          ON T1.joincol = T2.joincol
     INNER JOIN table3 T3
          ON T2.joincol2 = T3.joincol

Currently, in my application, I am selecting a TOP 1 from this query and putting it into a DataTable object. From there, I determine the type by looking at the DataType property for each of the columns. However, I need to make the distinction between varchar and nvarchar.

I need to find a way to get the data types of the columns in the results of this query. For example, the data type of col1, col2, alias1, cast1, cast2, alias2. Is there a way to do this in the SQL query itself? I know there is sys.columns, but it only has the information for the source table and I'm not sure how to handle the cases above where I am aliasing columns and casting.

Alternatively, is there a way to do this on the C# side? In both cases where I am selecting varchar and nvarchar types from SQL, it puts it into the DataTable as a System.String. Is there some property in the DataType accessor that will indicate whether or not it is varchar or nvarchar? Or is there another way to determine this using the DataTable or something else?

nickkoko
  • 1
  • 1
  • [sys. dm\_exec\_describe\_first\_result\_set](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-describe-first-result-set-transact-sql?view=sql-server-ver16)? – Thom A Mar 02 '23 at 13:48
  • 1
    Also: [Bad Habits to Kick : Declaring VARCHAR without (length)](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length) – Thom A Mar 02 '23 at 13:48
  • It *is* possible to do on the C# side (`SqlDataReader.GetSchemaTable`), but it's not very comfortable (the result is a dictionary with specific keys you have to know about). `sp_describe_first_result_set` is more comfortable if you have it. – Jeroen Mostert Mar 02 '23 at 13:50
  • Does this answer your question? https://stackoverflow.com/a/681669/13061224 – siggemannen Mar 02 '23 at 14:19
  • I would suggest to can varchar though, the world has moved on from ascii a while ago, why not just cast to nvarchar – siggemannen Mar 02 '23 at 14:20
  • Another way to get SQL type name client-side is with a `SELECT TOP 0 ...` query and use SqlDataReader.GetDataTypeName() for each column. – Dan Guzman Mar 02 '23 at 14:26
  • @JeroenMostert Both of those worked, thanks! I went with `SqlDataReader.GetSchemaTable` and used the `DataTypeName` column to identify varchar vs nvarchar. Thank you! – nickkoko Mar 02 '23 at 17:50

0 Answers0