SDU Tools: List User-Defined Data Types in SQL Server

Our free SDU Tools for developers and DBAs, now includes a very large number of tools, with procedures, functions, and views. I do a lot of reviewing of database designs and one thing I always check for is the use of user-defined data types. I’ve had situations where these have caused me substantial issues. So we added a tool that can help to find these. It’s called ListUserDefinedDataTypes.

The procedure takes one parameter.

@DatabaseName is the database to process

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below. The Azure SQL DB version that only operates on the current database is simpler and can be seen by downloading the tools.

CREATE PROCEDURE SDU_Tools.ListUserDefinedDataTypes
@DatabaseName sysname
AS
BEGIN

-- Function:      Lists the users-defined data types in a database
-- Parameters:    @DatabaseName sysname         -> Database to process
-- Action:        Lists the user-defined data types
-- Return:        Rowset containing SchemaName, TableName, ColumnName, and DataType. Within each 
-- table, columns are listed in column ID order
-- Refer to this video: https://youtu.be/Xay5qdsSJ7Y
--
-- Test examples: 
/*

EXEC SDU_Tools.ListUserDefinedDataTypes @DatabaseName = N'AdventureWorks';

*/
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    
    DECLARE @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

    DECLARE @SQL nvarchar(max) = 
'   SELECT typ.[name] + CASE WHEN typ.[name] IN (N''decimal'', N''numeric'')
                             THEN N''('' + CAST(typ.precision AS nvarchar(20)) + N'', '' 
                                  + CAST(typ.scale AS nvarchar(20)) + N'')''
                             WHEN typ.[name] IN (N''varchar'', N''nvarchar'', N''char'', N''nchar'', N''binary'', N''varbinary'')
                             THEN N''('' + CASE WHEN typ.max_length < 0 
                                              THEN N''max'' 
                                                WHEN typ.[name] IN (N''nvarchar'', N''char'')
                                                THEN CAST(typ.max_length / 2 AS nvarchar(20)) 
                                                ELSE CAST(typ.max_length AS nvarchar(20)) 
                                         END + N'')''
                             WHEN typ.[name] IN (N''time'', N''datetime2'', N''datetimeoffset'')
                             THEN N''('' + CAST(typ.scale AS nvarchar(20)) + N'')''
                             ELSE N''''
                        END AS DataType,
           typb.[name] + CASE WHEN typb.[name] IN (N''decimal'', N''numeric'')
                             THEN N''('' + CAST(typ.precision AS nvarchar(20)) + N'', '' 
                                  + CAST(typ.scale AS nvarchar(20)) + N'')''
                             WHEN typb.[name] IN (N''varchar'', N''nvarchar'', N''char'', N''nchar'', N''binary'', N''varbinary'')
                             THEN N''('' + CASE WHEN typ.max_length < 0 
                                              THEN N''max'' 
                                                WHEN typb.[name] IN (N''nvarchar'', N''char'')
                                                THEN CAST(typ.max_length / 2 AS nvarchar(20)) 
                                                ELSE CAST(typ.max_length AS nvarchar(20)) 
                                         END + N'')''
                             WHEN typb.[name] IN (N''time'', N''datetime2'', N''datetimeoffset'')
                             THEN N''('' + CAST(typ.scale AS nvarchar(20)) + N'')''
                             ELSE N''''
                        END AS SystemDataType
    FROM ' + QUOTENAME(@DatabaseName) + N'.sys.[types] AS typ 
    INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.[types] AS typb 
        ON typ.system_type_id = typb.system_type_id
        AND typ.system_type_id = typb.user_type_id 
    WHERE typ.is_user_defined = 1
    ORDER BY DataType, SystemDataType;';
    EXEC (@SQL);
END;

2025-03-09