List your User-Defined Types (UDT)

User defined types (UDT) allow the database designer to give an alias to a standard type. This is an aid to insure that there is uniformity between columns that are used to hold similar information. For example, I generally defined the type AppDescription as varchar(2000) and use it for general purpose description columns.

As I've been programming replication UDTs have become a problem. SQL Server will move the definition of a table into a subscription database as part of a snapshot and it will keep the UDT as part of the definition if you ask. However, it won't move the definition of the UDT to the subscription database. You have to create any UDTs that you want to use between the time that the database is created and the time that the snapshot is first applied.

Due to this issue I've been reviewing all my UDT usage on my current project and this week's UDT was written to help. udf_UDT_UsedTAB returns a table of the columns where a UDT is used in a database. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  FUNCTION dbo.udf_UDT_UsedTAB (

    @typename sysname = NULL -- UDT to locate or Null for all
) RETURNS TABLE
/*
* Returns a table of columns that are defined with 
* User-Defined Types (UDT).  Does not included the 
* UDTs that SQL Server creates such as money or sysname.
*
* Example:
select * from dbo.udf_UDT_UsedTAB(null)
*      
* © Copyright 2004 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published as T-SQL UDF of Week Newsletter Vol 2 #19 04/23/04 
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS RETURN 

SELECT TOP 100 PERCENT WITH TIES 
       o.[name] [Table_Name]
     , c.[name] [Column_Name]
     , t.[name] [UDT]
     , dbo.udf_SQL_DataTypeString (u.[name]
                         ,t.length,t.prec, t.scale) [Data_Type]
    FROM systypes t
        INNER JOIN syscolumns c
            on t.xusertype = c.xusertype
        INNER JOIN sysobjects o
             on c.id = o.id
        INNER JOIN systypes u
            on t.xtype = u.xusertype
    WHERE (@Typename is null  or @typename = t.[name])
          and t.xusertype > 256
          and o.type = 'u'
    ORDER BY o.[Name]
           , c.[Name]


GO

GRANT SELECT on dbo.udf_UDT_UsedTAB TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

The parameter allows the output to be limited to the columns in a single table, however, I usually just executed it with a NULL parameter to see all the tables. Here's a query with partial results from the database that I'm working on:

select * from dbo.udf_UDT_UsedTAB(null)
GO

(Results - reformatted)
Table_Name          Column_Name      UDT            Data_Type     
------------------- ---------------- -------------- ------------- 
AppFile             CreatedByUserID  APPUserID      varchar(50)
AppReportDefinition CreatedByUSERID  APPUserID      varchar(50)
AppReportDefinition Description      AppDescription varchar(2000)
AppReportDefinition LastUpByUSERID   APPUserID      varchar(50)
AppReportParameter  CreatedByUSERID  APPUserID      varchar(50)
AppReportParameter  LastUpByUSERID   APPUserID      varchar(50)
AppSecurityHistory  CreatedByUSERID  APPUserID      varchar(50)
AppSecurityHistory  UserID           APPUserID      varchar(50)
Quote               Who_Changed      APPUserID      varchar(50)
Quote               Who_Created      APPUserID      varchar(50)
QuoteAttachment     Who_Changed      APPUserID      varchar(50)

Although the UDTs in my database have been causing me pain while implmenenting replication, I still find them useful. They'll play a larger role in the next version of SQL Server, which is due out early next year. More on that in coming issues.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick