If you ever need to get a list of data types in SQL Server, you can use one of the system views to do just that.
In particular, you can use the sys.types system catalog view. This view returns all system-supplied and user-defined data types defined in the database. If you’re using SQL Server 2000 sys.systypes should do the trick.
Example 1 – The sys.types View
The sys.types view is the best one to use if you’re using a version of SQL Server later than SQL Server 2000.
The quickest/easiest way to use the sys.types view is to select everything. In that case your code might look like this:
SELECT * FROM sys.types;
However, this returns a lot of columns that you may or may not be interested in. You can narrow it down to just those columns that you’re interested in.
Here’s an example of selecting some columns of interest:
SELECT name, max_length, [precision], scale, is_user_defined FROM sys.types;
Result:
+------------------+--------------+-------------+---------+-------------------+ | name | max_length | precision | scale | is_user_defined | |------------------+--------------+-------------+---------+-------------------| | image | 16 | 0 | 0 | 0 | | text | 16 | 0 | 0 | 0 | | uniqueidentifier | 16 | 0 | 0 | 0 | | date | 3 | 10 | 0 | 0 | | time | 5 | 16 | 7 | 0 | | datetime2 | 8 | 27 | 7 | 0 | | datetimeoffset | 10 | 34 | 7 | 0 | | tinyint | 1 | 3 | 0 | 0 | | smallint | 2 | 5 | 0 | 0 | | int | 4 | 10 | 0 | 0 | | smalldatetime | 4 | 16 | 0 | 0 | | real | 4 | 24 | 0 | 0 | | money | 8 | 19 | 4 | 0 | | datetime | 8 | 23 | 3 | 0 | | float | 8 | 53 | 0 | 0 | | sql_variant | 8016 | 0 | 0 | 0 | | ntext | 16 | 0 | 0 | 0 | | bit | 1 | 1 | 0 | 0 | | decimal | 17 | 38 | 38 | 0 | | numeric | 17 | 38 | 38 | 0 | | smallmoney | 4 | 10 | 4 | 0 | | bigint | 8 | 19 | 0 | 0 | | hierarchyid | 892 | 0 | 0 | 0 | | geometry | -1 | 0 | 0 | 0 | | geography | -1 | 0 | 0 | 0 | | varbinary | 8000 | 0 | 0 | 0 | | varchar | 8000 | 0 | 0 | 0 | | binary | 8000 | 0 | 0 | 0 | | char | 8000 | 0 | 0 | 0 | | timestamp | 8 | 0 | 0 | 0 | | nvarchar | 8000 | 0 | 0 | 0 | | nchar | 8000 | 0 | 0 | 0 | | xml | -1 | 0 | 0 | 0 | | sysname | 256 | 0 | 0 | 0 | +------------------+--------------+-------------+---------+-------------------+
These are the data types within the current database. If you switch to a different database, you could get different results (this will depend largely on whether any user-defined data types or alias data types have been defined, and whether or not they differ between the databases).
Example 2 – Alias & User-Defined Data Types
In the previous example, I included the is_user_defined column. We can see that none of the data types are user-defined or alias data types, because they all have 0 as their value.
Let’s create an alias data type, and then see how it appears in the results.
Create the alias data type:
CREATE TYPE SSN FROM varchar(11) NOT NULL;
Now let’s run the query again.
SELECT name, max_length, [precision], scale, is_user_defined FROM sys.types;
Result:
+------------------+--------------+-------------+---------+-------------------+ | name | max_length | precision | scale | is_user_defined | |------------------+--------------+-------------+---------+-------------------| | image | 16 | 0 | 0 | 0 | | text | 16 | 0 | 0 | 0 | | uniqueidentifier | 16 | 0 | 0 | 0 | | date | 3 | 10 | 0 | 0 | | time | 5 | 16 | 7 | 0 | | datetime2 | 8 | 27 | 7 | 0 | | datetimeoffset | 10 | 34 | 7 | 0 | | tinyint | 1 | 3 | 0 | 0 | | smallint | 2 | 5 | 0 | 0 | | int | 4 | 10 | 0 | 0 | | smalldatetime | 4 | 16 | 0 | 0 | | real | 4 | 24 | 0 | 0 | | money | 8 | 19 | 4 | 0 | | datetime | 8 | 23 | 3 | 0 | | float | 8 | 53 | 0 | 0 | | sql_variant | 8016 | 0 | 0 | 0 | | ntext | 16 | 0 | 0 | 0 | | bit | 1 | 1 | 0 | 0 | | decimal | 17 | 38 | 38 | 0 | | numeric | 17 | 38 | 38 | 0 | | smallmoney | 4 | 10 | 4 | 0 | | bigint | 8 | 19 | 0 | 0 | | hierarchyid | 892 | 0 | 0 | 0 | | geometry | -1 | 0 | 0 | 0 | | geography | -1 | 0 | 0 | 0 | | varbinary | 8000 | 0 | 0 | 0 | | varchar | 8000 | 0 | 0 | 0 | | binary | 8000 | 0 | 0 | 0 | | char | 8000 | 0 | 0 | 0 | | timestamp | 8 | 0 | 0 | 0 | | nvarchar | 8000 | 0 | 0 | 0 | | nchar | 8000 | 0 | 0 | 0 | | xml | -1 | 0 | 0 | 0 | | sysname | 256 | 0 | 0 | 0 | | SSN | 11 | 0 | 0 | 1 | +------------------+--------------+-------------+---------+-------------------+
The new data type appears (at the bottom). It also has a value of 1 in the is_user_defined column.
The sys.systypes View
There’s also a sys.systypes system compatibility view, which is only provided for backward compatibility. This is because systypes was the name of a system table in SQL Server 2000, and there could be a lot of legacy systems out there with code that references that table.
Microsoft has indicated that the sys.systypes system compatibility view will be removed in a future version of Microsoft SQL Server, and therefore recommends that you avoid using this view in new work.
In other words, use sys.types instead if you can.
But if you’re still using an earlier version of SQL Server, you will need to use sys.systypes.
Note that sys.systypes contains different columns/column names to sys.types, so the column names I used in the previous example won’t work.
You can still select all columns using the asterisk (*) wildcard character though:
SELECT * FROM sys.systypes;