ContentTypeIds In WSS3
In WSS3, if you execute the following SQL:
|
1 2 3 4 5 |
<span style="font-family: Courier;"> <span style="color: #008000;">SELECT</span> <span style="color: #000000;">ContentTypeId</span> <span style="color: #008000;">FROM </span> <span style="color: #000000;">ContentTypes</span> </span> |
You will notice that the content types are represented in the output as hex. If you take a look at the table definition, you’ll see that the actual data type of the column is VARBINARY(512).
Doing a lookup like so:
|
1 2 3 4 5 6 7 |
<span style="font-family: Courier;"> <span style="color: #008000;">SELECT</span> <span style="color: #000000;">ContentTypeId</span> <span style="color: #008000;">FROM </span> <span style="color: #000000;">ContentTypes</span> <span style="color: #008000;">WHERE </span> <span style="color: #000000;">ContentTypeId = </span><span style="color: #ff00ff;">'0x101'</span> </span> |
Will not work since you cannot perform a comparison between VARBINARY and a character data type directly.
Doing the following will also not work:
|
1 2 3 4 5 6 7 |
<span style="font-family: Courier;"> <span style="color: #008000;">SELECT</span> <span style="color: #000000;">ContentTypeId</span> <span style="color: #008000;">FROM </span> <span style="color: #000000;">ContentTypes</span> <span style="color: #008000;">WHERE </span> <span style="color: #ff0000;">CAST</span><span style="color: #000000;">(ContentTypeId </span><span style="color: #008000;">AS </span><span style="color: #000000;">VARCHAR(512)) = </span><span style="color: #ff00ff;">'0x101'</span> </span> |
This doesn’t work because the underlying type of the binary data isn’t character data. It’s integer data. You can confirm this by running the following query:
|
1 2 3 4 5 6 |
<span style="font-family: Courier;"> <span style="color: #008000;">SELECT</span> <span style="color: #000000;">ContentTypeId,</span> <span style="color: #ff0000;">CAST</span><span style="color: #000000;">(ContentTypeId </span><span style="color: #008000;">AS </span><span style="color: #000000;">VARCHAR(512))</span> <span style="color: #008000;">FROM </span> <span style="color: #000000;">ContentTypes</span> </span> |
You’ll see that it’s just a bunch of gibberish. Try the same query with INT and you’ll see that the data makes much more sense. What you’ll notice is that content types that inherit from a base content type will have numerical values that increment by 1.
This information is useful, but not nearly as useful as the data that you can glean from the hex string representation of the ContentTypeId. You see, in the hex string representation, the base ID is a substring of the ID of any inheriting content type. For example, if I have a content type which has a ID (as a hex string) of 0x0101345346345312234346, then any child content types will have 0x0101345346345312234346 as a substring (e.g. 0x010134534634531223434601, 0x010134534634531223434602).
So how do we get this data in SQL Server for comparison purposes? We need to use an “undocumented” SQL function: fn_varbintohexstr().
This allows you to do nifty queries to find a given content type and all child content types (or any query where you have to retrieve information about a hierarchy of content types) like so:
|
1 2 3 4 5 6 7 8 |
<span style="font-family: Courier;"> <span style="color: #008000;">SELECT</span> <span style="color: #000000;">*</span> <span style="color: #008000;">FROM </span> <span style="color: #000000;">ContentTypes</span> <span style="color: #008000;">WHERE </span> <span style="color: #000000;"><strong>master.dbo.fn_varbintohexstr</strong>(ContentTypeId) </span></span><span style="font-family: Courier;"><span style="color: #000000;">LIKE </span> <span style="color: #ff00ff;">'0x0101345346345312234346%'</span> </span> |
You can find out more information on this function here.
