Finding SQL Base Type for a User-Defined Type

Question

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?

This method looks reasonable. Are you having any particular issues with it? — John Dewey, May 21 '12 at 17:26
My only issue is that the input type in this procedure is sysname which is an odd choice as an input type. It seems to work fine when I pass in a string, but it makes me a bit nervous. — Aaron L., May 21 '12 at 18:19
`sysname` is just an alias for `nvarchar`, what about this makes you nervous? — Aaron Bertrand, May 21 '12 at 22:25
The answer in this post made me question whether or not it was a good idea to use that type: [link](http://stackoverflow.com/questions/5720212/what-is-sysname-data-type-in-sql-server) I suppose that I don't need to worry if it is simply just another way to say nvarchar(128) NOT NULL — Aaron L., May 21 '12 at 22:29

Aaron Bertrand · Accepted Answer · 2020-05-13T02:21:11.043

9

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

edited May 13 '20 at 02:21

answered May 21 '12 at 17:33

Aaron Bertrand

272,866
37
466
490

Thanks for answering. What I really need though is the underlying SQL type for a user-defined type. The name column in the statement you suggested still returns the name of the user defined type, not the sql type that it represents. – Aaron L. May 21 '12 at 18:39
@AaronL. sorry, missed that in the output. Try now please – Aaron Bertrand May 21 '12 at 18:45
I think that will get me close enough to what I need. Thank you. It is surprising to, however, me that there is no table that holds the names of the user-defined types and the properties of the sql types that they relate to. I wonder what MS uses to populate the list of user defined types in SSMS? That list always contains the usertype and it corresponding SQL type. – Aaron L. May 21 '12 at 22:18
@AaronL. what do you mean? They're in `sys.types` as my query demonstrated... they just don't repeat the name in the row (this is called normalization) - they point to other rows in the `sys.types` table to derive the base type information. The Management Studio code would use a join quite similar to the one I've written above... of course you can run Profiler to see exactly what it does. – Aaron Bertrand May 21 '12 at 22:19
Ah, I see now. I was apparently just being daft. Thanks again for the help. I appreciate it. – Aaron L. May 21 '12 at 22:24

limit · Answer 2 · 2014-12-02T18:36:07.703

To get the information for all the user-defined types in a database you can use INFORMATION_SCHEMA.DOMAINS:

SELECT 
   DOMAIN_SCHEMA,
   DOMAIN_NAME,
   DATA_TYPE,
   NUMERIC_PRECISION,
   NUMERIC_SCALE,
   DATETIME_PRECISION,
   CHARACTER_MAXIMUM_LENGTH

FROM 
   INFORMATION_SCHEMA.DOMAINS

UPD: The following query return human readable definition for all user defind types:

  SELECT 
     DOMAIN_SCHEMA + '.' + DOMAIN_NAME AS TypeName,
     DATA_TYPE + 
        COALESCE('('+ 
           CAST(COALESCE(NUMERIC_PRECISION,DATETIME_PRECISION,CHARACTER_MAXIMUM_LENGTH) AS VARCHAR) + 
           COALESCE(',' + CAST(NUMERIC_SCALE AS VARCHAR),'') +
        ')','') AS Definition

  FROM 
  (
     SELECT 
        DOMAIN_SCHEMA,
        DOMAIN_NAME,
        DATA_TYPE,
        CASE 
           WHEN DATA_TYPE IN ('tinyint','smallint','int','bigint') THEN NULL
           ELSE NUMERIC_PRECISION
        END AS NUMERIC_PRECISION,
        NUMERIC_SCALE,
        CASE 
           WHEN DATA_TYPE IN ('smalldatetime','datetime','date') THEN NULL
           ELSE DATETIME_PRECISION
        END AS DATETIME_PRECISION,
        CHARACTER_MAXIMUM_LENGTH

     FROM 
        INFORMATION_SCHEMA.DOMAINS
  ) T

(tested only for types that i am using myself)

score 0 · Answer 3 · answered Jul 19 '23 at 18:38

To get the underlying, system type name of a user-defined type, along with its attributes:

USE AdventureWorks
SELECT 
    u.name AS userdefined,
    s.name AS underlying,
    u.precision,
    u.scale,
    u.max_length
FROM sys.types u
INNER JOIN sys.types s
    ON u.system_type_id = s.user_type_id
WHERE u.is_user_defined = 1
    AND u.name = 'Name'

Result:

userdefined underlying  precision   scale   max_length
Name        nvarchar    0           0       100

So, the user-defined type 'Name' in AdventureWorks is in fact an alias for nvarchar(100).

[rant]

Given that MS SQL user-defined types are just aliases for an underlying system type, they are little more than decorations.

For example, AFAIK, it is not possible to define a user-defined type 'Rational' consisting of 2 Integers and the associated arithmetic.

[/rant]**

Finding SQL Base Type for a User-Defined Type

3 Answers3

Linked