ContentTypeIds In WSS3

In WSS3, if you execute the following SQL:

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:

Will not work since you cannot perform a comparison between VARBINARY and a character data type directly.

Doing the following will also not work:

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:

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:

You can find out more information on this function here.

You may also like...