TIE Fighters and sys.types: “There’s Too Many of Them!”
As the Y-Wing pilot in Star Wars: Episode VI – Return of the Jedi proclaimed, sometimes there’s too many of them. He was referring to TIE Fighters during the assault on the second Death Star. I echoed the sentiment while querying the system catalog for a client’s database. I was building the foundation for a data dictionary (at the end of the project, unfortunately) and started by pulling a list of all the fields in the database along with their data types. I was dismayed when my tables returned more records than there were fields. There were too many of them.
To show why this happenned, I will start by creating a simple table.
CREATE TABLE dbo.TooManyOfThem
(
RecordID int NOT NULL
, RecordName nvarchar(100) NULL
)
Notice that I have an nvarchar field in there. It turns out, that was the culprit. Let’s query the system catalog to return the columns from this table along with their associated data types.
SELECT
o.name AS TableName
, c.name As ColumnName
, t.name AS DataTypeName
FROM sys.columns c
INNER JOIN sys.objects o
ON o.object_id = c.object_id
INNER JOIN sys.types t
ON t.system_type_id = c.system_type_id
WHERE o.name = 'TooManyOfThem'
Since my TooManyOfThem table has two columns, I would expect to get two records returned by this query. Here’s is what I get.
TableName ColumnName DataTypeName
-------------------- --------------- ------------
TooManyOfThem RecordID int
TooManyOfThem RecordName nvarchar
TooManyOfThem RecordName sysname
(3 row(s) affected)
I have a bad feeling about this. These aren’t the results I’m looking for. Are my fields riding single file to hide their numbers?
My RecordName field is showing up more than once. Notice that the second occurrence has DataTypeName of “sysname.” I reached out with my feelings and modified my query to get more fields returned.
SELECT
o.name AS TableName
, c.name As ColumnName
, c.system_type_id AS C_system_type_id
, c.user_type_id AS C_user_tpe_id
, t.system_type_id AS T_system_type_id
, t.user_type_id AS T_user_type_id
, t.name AS DataTypeName
FROM sys.columns c
INNER JOIN sys.objects o
ON o.object_id = c.object_id
INNER JOIN sys.types t
ON t.system_type_id = c.system_type_id
WHERE o.name = 'TooManyOfThem'
When I run this new query, I get the following results.
TableName ColumnName C_system_type_id C_user_tpe_id T_system_type_id T_user_type_id DataTypeName
-------------------- --------------- ---------------- ------------- ---------------- -------------- ------------
TooManyOfThem RecordID 56 56 56 56 int
TooManyOfThem RecordName 231 231 231 231 nvarchar
TooManyOfThem RecordName 231 231 231 256 sysname
(3 row(s) affected)
We see that in sys.types, the system_type_id of 231 leads to both “nvarchar” and “sysname.” Let’s write a query to return the types whose system_type_id values appear more than once.
SELECT
system_type_id
, user_type_id
, name
FROM sys.types
WHERE system_type_id IN
(
SELECT
system_type_id
FROM sys.types
GROUP BY system_type_id
HAVING COUNT(*) > 1
)
We get the following results.
system_type_id user_type_id name
-------------- ------------ ---------------
240 128 hierarchyid
240 129 geometry
240 130 geography
231 231 nvarchar
231 256 sysname
(5 row(s) affected)
As we see, the system_type_id is not unique in sys.types. The user_type_id, however, does not repeat. So, I change my join to the sys.types view from system_type_id to user_type_id.
SELECT
o.name AS TableName
, name As ColumnName
, name AS DataTypeName
FROM sys.columns c
INNER JOIN sys.objects o
ON o.object_id = c.object_id
INNER JOIN sys.types t
ON t.user_type_id = c.user_type_id
WHERE o.name = 'TooManyOfThem'
Now I get the results I’m looking for.
TableName ColumnName DataTypeName
-------------------- --------------- ------------
TooManyOfThem RecordID int
TooManyOfThem RecordName nvarchar
(2 row(s) affected)
So, the path you choose will certainly affect your destiny. Here’s another path for you: Robot Chicken Star Wars. Seriously. It’s some of the funniest stuff I’ve ever seen.
