Archives

User Defined Data Type Definition Script

Recently I worked on a project where every data type was user defined. In case you weren’t aware, SQL Server supports the use of User Defined Data Types. These are custom data types which are based on the standard types. For example, if your data always uses a two character string for states, you may want to create a data type called UDT_State.

However, this project had 40 UDTs and their names were fairly ambiguous. So, I created a “cheat sheet” script to refer to when working in that environment.

First, let’s create some user defined data types.

use tempdb
go
CREATE TYPE [dbo].[UDT_State] FROM char(2) NOT NULL;
GO
CREATE TYPE [dbo].[UDT_User_Id] FROM Int NOT NULL;
GO
CREATE TYPE [dbo].[UDT_Cost] FROM decimal(10, 3) NULL;
GO

The following script is compatible with SQL 2005 through 2008 R2. I believe it also works on SQL 2000 but don’t have access to a server to test it.

SELECT ST.[name] AS UDTName,
       CASE
          WHEN ST1.[name] LIKE '%char'
          THEN
             ST1.[name] + '(' + cast (ST.max_length AS VARCHAR (5)) + ')'
          WHEN ST1.[name] = 'numeric' OR ST1.[name] = 'decimal'
          THEN
               ST1.[name]
             + '(' + cast (ST.[precision] AS VARCHAR (5))
             + ',' + Cast (ST.scale AS VARCHAR (5)) + ')'
          ELSE
             ST1.[name]
       END AS BaseType,
       CASE ST.is_nullable WHEN 1 THEN 'NULL' ELSE 'NOT NULL' END AS Nullable
  FROM    sys.types ST
       JOIN
          sys.types ST1
       ON ST1.user_type_id = ST.system_type_id
 WHERE ST.is_user_defined = 1

Special thanks to Jeff Rush (Blog/Twitter) for the idea to write this script.

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>