Finding SQL Base Type for a User-Defined Type
sql, sql-server-2008
Solution
You shouldn't be using systypes or syscolumns - these are backward compatibility views, and sys.types and sys.columns are highly preferred unless you are trying to write code that works on SQL Server 2000+ (which I don't recommend either).
To get the information about a type you already know the name of:
SELECT name, precision, scale, max_length
FROM sys.types AS t
WHERE name = 'bVendor';
To get the information for all the user-defined types in a database:
SELECT name, precision, scale, max_length
FROM sys.types AS t
WHERE is_user_defined = 1;
To get the information about all the types (system and user-defined) for a specific table:
UPDATE to include the base type:
SELECT
[column] = c.name,
[base type] = COALESCE(bt.name, t.name),
[defined type] = t.name,
t.precision,
t.scale,
t.max_length
FROM sys.columns AS c
INNER JOIN sys.types AS t
ON c.system_type_id = t.system_type_id
AND c.user_type_id = t.user_type_id
LEFT OUTER JOIN sys.types AS bt
ON t.is_user_defined = 1
AND bt.is_user_defined = 0
AND t.system_type_id = bt.system_type_id
AND t.user_type_id <> bt.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.your_table_name');
Note that this will return two rows if you use alias types (e.g. `CREATE TYPE blat FROM nvarchar(32);`). If you really must use those (I recommend against them, also), then change the join clause to:
ON t.is_user_defined = 1
AND bt.is_user_defined = 0
AND t.system_type_id = bt.system_type_id
AND bt.user_type_id = bt.system_type_id
Problem
I am working with a legacy database that has a large number of user-defined SQL types. I am writing a method in .NET in which I am defining parameters in the SqlParameter object. I need the underlying SQL types for the user defined types in order to properly define the parameters as I create them dynamically at runtime. To do this I created this procedure: ``` (@typename sysname) AS SET NOCOUNT ON SELECT distinct st.name as UserType, t.precision, t.max_length, bt.name as BaseType FROM dbo.syscolumns c INNER JOIN dbo.systypes st ON st.xusertype = c.xusertype INNER JOIN dbo.systypes bt ON bt.xusertype = c.xtype inner join sys.types t on st.name = t.name WHERE st.name = 'bVendor' ``` I am wondering if this is the best way to go about getting the underlying base type for a user defined type?