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:

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:

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.