I just ran into an odd little problem. And of course, solving it turned out to be kind of fun. Since I don’t do a ton of development work, this little challenge took some thinking.

We’re building a little datamart, and I want to pull a few tables from the source database onto a different server. This shouldn’t be a problem, right? SSMS will generate the scripts to recreate the tables for me. That’s when I ran into a snag. This database uses a user defined data type for every column in the database. Literally every column. 1501 user defined data types. This is the same database that names the customer orders table CO and the purchase orders table PO. Yeah, that one.

The answer hit me in the shower this morning. UDTs are stored in system tables just like anything else. With a little bit of spelunking through the system tables, I had my answer.

select so.name, sc.name, st.name, sc.max_length
from sys.objects so
inner join sys.columns sc
on so.object_id = sc.object_id
inner join sys.types ut
on sc.user_type_id = ut.user_type_id
inner join sys.types st
on ut.system_type_id = st.user_type_id
where so.name IN (‘co’, ‘po’)
order by so.name, sc.column_id

By throwing this into Crystal Reports with a little formatting, I saved myself hours of work. It’s not pretty, but it certainly works.