TIE Fighters and sys.types: “There’s Too Many of Them!”

19 July, 2010 (08:39) | SQL, T-SQL | By: Mark V

Y-Wing 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.

 

 

 

 

 

Write a comment