Getting metadata from Catalog Views

Some days the interesting questions just present themselves. A few days ago, I had one of those days. To generate a datamodel for a data warehouse, one of the companies I work with uses a generator. This generator uses metadata of existing objects to generate SSIS packages and data models. After a change in their code, the generator didn’t return specific columns from objects anymore. After some searching, we found out that the mapping between the metadata of the datatype of the table, and the generator code, was done on the full datatype (for example, “varchar(25)” was used instead of “varchar”).

Fixing this bug was pretty easy. But they also had code to generate objects from metadata for user-defined datatypes (or aliases). But this code didn’t work as expected. So while fixing that, I thought that might make an interesting blog post. So let me show you how that works.

 
Create resources
In order to test this, I needed to create a user-defined datatype:

CREATE TYPE CSTM_DATATYPE FROM varchar(10) NOT NULL;

 
This creates a user-defined datatype for varchar, with a maximum length of 10. So basically, it’s an alias or derivative of an excisting datatype with a maximum precision / scale.

We also need a table that contains that datatype. In order to make this test a little easier, I also added the original datatype:

CREATE TABLE dbo.TEST_DATATYPE_MAPPING
(ID INT IDENTITY(1,1),
 STRING_NORMAL VARCHAR(10),
 STRING_CUSTOM CSTM_DATATYPE)

 
To retrieve the data from the table, I use a stored procedure:

CREATE PROC dbo.TEST_METADATA_OUTPUT
AS


SELECT
  ID,
  STRING_NORMAL,
  STRING_CUSTOM
FROM TEST_DATATYPE_MAPPING

 
Retrieving metadata
Now that the resources are created, we can check the metadata of the table:

SELECT
	column_ordinal,
	name,
	system_type_id,
	system_type_name,
	user_type_id,
	user_type_name
FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('[dbo].[TEST_METADATA_OUTPUT]'), 0) DRS

 
The metadata of the table shows one “normal” system_type_name (on STRING_NORMAL), and one empty system_type_name (on STRING_CUSTOM):

 
But what if you don’t want to use the metadata to query this information, but you just want to retrieve this information from the catalog views? You can use the sys.tables, sys.columns and sys.types catalog views:

SELECT DISTINCT
	T.name,
	C.name,
	SDT.system_type_id,
	SDT.name,
	UDT.user_type_id,
	UDT.name
FROM sys.tables T
INNER JOIN sys.columns C ON C.object_id = T.object_id
LEFT JOIN sys.types AS SDT ON C.system_type_id = SDT.system_type_id AND SDT.is_user_defined = 0
LEFT JOIN sys.types AS UDT ON C.user_type_id = UDT.user_type_id AND UDT.is_user_defined = 1
WHERE 1 = 1
AND T.name = 'TEST_DATATYPE_MAPPING'

 
This query will give you the following information:

 
But you can also get the same information, while using the sys.dm_exec_describe_first_result_set_for_object. You just need to join the sys.types on either the system_type_id or the user_type_id:

SELECT DISTINCT
  DRS.name,
  DRS.column_ordinal,
  T.system_type_id,
  T.name,
  TC.user_type_id,
  TC.name,
  TC.max_length
FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('[dbo].[TEST_METADATA_OUTPUT]'), 0) DRS
LEFT JOIN sys.types AS T ON DRS.system_type_id = T.system_type_id AND T.is_user_defined = 0
LEFT JOIN sys.types AS TC ON DRS.user_type_id = TC.user_type_id AND TC.is_user_defined = 1

 
This will output the same information:

 
Conclusion
Even though I haven’t seen that many companies that use user-defined datatypes lately, it’s definately important that it works when you use a generator for your SSIS packages or data model. So making sure you have the correct datatypes is a must! So I’m glad I could help them fixing this query. Now I know for sure they’ll see the correct data.