I’ve been working on a utility that takes in XML documents and dumps them into a database table. This is a generic solution that will be reused with many different types of XML documents, so I looked into ways to make the utility smarter, to make adding new XML document types easier.

One thought I had was with casting. All the values start out as strings (since they’re from XML), but upon inserting into the database, they need to be converted to the correct data types. Rather than putting this information in the code, what if the code was able to ask the database what data type a column is, and then cast it to that type automatically?

After some digging and experimentation, I came up with this query:

SELECT
  C.name AS ColumnName,
  T.name As DataType,
  C.max_length AS Length
FROM sys.columns AS C JOIN sys.types AS T ON C.user_type_id = T.user_type_id
WHERE c.object_id = OBJECT_ID('YOUR_TABLE_NAME_HERE')
ORDER BY c.column_id;

Which will give you something like this:

ColumnName  DataType  Length
==========  ========  ======
ID          int       4
Title       varchar   255
Blurb       varchar   255
IsActive    bit       1
Created     datetime  8

Still not sure I’ll use this. Might be a little too much magic. But I thought it was a pretty cool trick worth sharing.

Adapted from this blog post with help from this documentation.