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.
Popular Articles