How to extract IDENTITY_SEED and IDENTITY_INCREMENT from autoval column in syscolumns

There are many hints on the internet that in the autoval column in the system table syscolumns in MS SQL Server 2000 it is stored the identity seed and identity increment values for columns that have been set as identity. Problem is there are little clues on how to extract those values from that column, because the data is stored as a binary value.

After some testing on my side I found that the autoval column can split in four parts, two of which represent IDENTITY_SEED and IDENTITY_INCREMENT values. But there is a catch - those binary data of each segment must be first reversed and then the values can be extracted. Here is the script:

T-SQL:
SELECT tCol.[name]
        , IDENTITY_INCREMENT = CAST(CAST(REVERSE(SUBSTRING(autoval, 5, 4)) AS VARBINARY(8)) AS BIGINT)
        , IDENTITY_SEED = CAST(CAST(REVERSE(SUBSTRING(autoval, 9, 4)) AS VARBINARY(8)) AS BIGINT)
FROM [syscolumns] tCol
WHERE tCol.autoval IS NOT NULL
You would ask: "Why the heck to we need such complex query when there are built in function like OBJECTPROPERTY and COLUMNPRPERTY that retrieve those values?". Well if you are working with linked servers, and you want to retrieve the values from the remote server and you are connected to the local one, those functions do not work ;)

Labels: ,