identifying deleted columns

Question

Hi, i am using sql server 2000 .I have a table with four columns ie. col1,col2,col3 and col4. I have records in the table . I used dbcc page command and i could see the values of all the column. I then deleted a column , col2, from the table and did dbcc page again. I could see the values of all the columns except for col2 which value "Dropped" (The record structure was still the same). I understand that the column information are stored in "syscolumn" table However the record for col2 was not found(for obvious reason). I am wondering in which system table can i find the information the col2 is deleted. Where is this information stored?

Thanks, Jude

Answer 1

I can only answer for 2005, although that might give you enough of an indication for 2000. The information is not available as far as I am aware via DMV's, to get at the information you would need to use the Dedicated Admin Console (DAC).

Using the DAC connection the tables columns can be found in sys.sysrowsetsolumns - if you filter that down to the individual rowsetid / hobtid of the object you can see your columns and there are two fields to note, status and rowsetcolid.

As a test case, I output the value of the query twice, before and after dropping the column.

  select * from sys.sysrowsetcolumns where rowsetid = 72057594038845440
  ALTER TABLE dbo.foo DROP COLUMN test2
  select * from sys.sysrowsetcolumns where rowsetid = 72057594038845440

Output was

rowsetid           rowsetcolid hobtcolid status rcmodified maxinrowlen
72057594038845440  1           1           0        0    4
72057594038845440  2           2           0     0    4
72057594038845440  3           3           0     0    4
72057594038845440  4           4           0     0    4

rowsetid           rowsetcolid hobtcolid status rcmodified maxinrowlen
72057594038845440  1           1           0        0    4
72057594038845440  2           2           0     0    4
72057594038845440  4           4           0     0    4
72057594038845440  65536       3           2     0    4

The test was dropping column 3 and you can see hobtcolid 3 has altered the status to 2 and the rowsetcolid has been set to 65536. What the status and id mean I can only infer from the actions taken.

Addendum : Do not edit any data in the system tables via the DAC - very very risky.