In my last article - What happens when we drop a column on a SQL Server table? Where's my space? - I have shown what happens when we drop a column from a table. Today, we are going to check if we have a way to find tables with dropped columns.

Why?

As we learnt, when we drop a column this is just a metadata operation and won’t clear the space that was being used. You may be asking - Do I have more tables with dropped columns that I’m not aware of? - And that is a legit question.

Let's see how we can achieve this.

A more complex, resource and time-consuming way

As we saw before we can check if a column was dropped from a table by inspecting a data page that belongs to it. This means, that probably we can use that in some way to double-check if that is the case or not for more tables.

Just text won't be very helpful

One of the examples that I have shown before was a dump of the output of the `DATA_PAGE` as text. A first knee-jerk reaction could be to use some `SUBSTRING`/`PATHINDEX` T-SQL functions to perform a search. However, that seems a little bit too much work.

Using DBCC PAGE...WITH TABLERESULTS

I haven't mentioned before but we can output the content of the page in a tabular way instead of the text format. For that, we just need to append the `WITH TABLERESULTS` keywords when running the `DBCC PAGE` command.

First, we need to get a `DATA_PAGE` id. As seen before, for that we need to get an `allocated_page_page_id` value from:

-- 2012 or newer
SELECT allocated_page_page_id
  FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.Client'), NULL, NULL, 'DETAILED')
 WHERE page_type_desc = 'DATA_PAGE'

Then, we can pick the value `544` and run the following code:

DBCC PAGE ('TableInternals', 1, 544, 3) WITH TABLERESULTS;

Just a quick reminder that undocumented commands (like DBCC PAGE) should be used with caution, as they are not officially supported by Microsoft and could potentially cause issues in your database.

We get the output in a table format like the following:

That said, we can run some extra lines of code to:

1. dump the page content to a temporary table (`#DBCCPAGE`)
2. run a T-SQL query to search and filter our results.

If you check carefully on the previous image, you can find, on the `Field` column, the value `DROPPED` highlighted below.

Let's filter by that.

IF OBJECT_ID('tempdb..#DBCCPAGE') IS NOT NULL
    DROP TABLE #DBCCPAGE;
GO
CREATE TABLE #DBCCPAGE (
    [ParentObject]  VARCHAR(255),
    [Object]        VARCHAR(255),
    [Field]         VARCHAR(255),
    [Value]         VARCHAR(255)
);

/* (1) */  
INSERT INTO #DBCCPAGE
EXECUTE ('DBCC PAGE (''TableInternals'', 1, 544, 3) WITH TABLERESULTS;');

/* (2) */  
SELECT *
  FROM #DBCCPAGE
WHERE Field = 'DROPPED'

This gives you an idea of one possibility to achieve this task. This example is just for one table and I'm sure you could build some extra code around it to make it check all tables.

However, that isn't necessary(!) because there is an easier, less expensive (resource-wise) and quicker way of doing it.

A better and faster approach - Relying on metadata

What if we had a less cumbersome way to check if we have a column dropped within a table?

Enter some metadata views! SQL Server provides some undocumented system internal views that hold some columns' metadata that are very useful. This system view, sys.system_internals_partition_columns, has an interesting column, called is_dropped, which, as you can imagine, will contain the value `1` when that same column was dropped but the table wasn't yet rebuilt.

Let's see it in action using our previous example. The first part of the script will create a `Client` table with 3 columns and insert one record.

DROP TABLE IF EXISTS Client
GO
CREATE TABLE Client
(
    Id          int NOT NULL identity(1,1),
    FirstName   varchar(50),
    DoB         datetime
)
GO

/* Insert one record */  
INSERT INTO Client (FirstName, DoB)
VALUES ('Alex', '1900-01-01')
GO

/* Show the record */  
SELECT Id, FirstName, DoB
  FROM Client
GO

Now, before we drop the `DoB` column, let's see what is returned on the `is_dropped` column of the `sys.system_internals_partition_columns` system view for our table.

For that, we can run the following code:

SELECT object_name(p.object_id) table_name,
        c.[name] as column_name,
        pc.partition_column_id,
        pc.is_dropped
  FROM sys.system_internals_partitions p
      JOIN sys.system_internals_partition_columns pc
        ON p.partition_id = pc.partition_id
      LEFT JOIN sys.columns c
        ON p.object_id = c.object_id
       AND pc.partition_column_id = c.column_id
 WHERE p.object_id = OBJECT_ID('Client')

We can see our 3 columns with sequential IDs (`partition_column_id`) and the `is_dropped` property with value `0`.

Now, let's drop the `DoB` column:

ALTER TABLE Client
DROP COLUMN DoB
GO

And rerun the previous query to check what has changed:

As we can see we lost a column name (that was the `DoB` column), the `partition_column_id` changed from `3` to `67108865` and now the `is_dropped` property has the value `1`.

That means if we filter only by the `is_dropped` property we can do a search on all tables of our database.

SELECT object_name(p.object_id) table_name,
        c.[name] as column_name,
        pc.partition_column_id,
        pc.is_dropped
  FROM sys.system_internals_partitions p
      JOIN sys.system_internals_partition_columns pc
        ON p.partition_id = pc.partition_id
      LEFT JOIN sys.columns c
        ON p.object_id = c.object_id
       AND pc.partition_column_id = c.column_id
 WHERE pc.is_dropped = 1

Cool stuff!

NOTE: With this query, if you have dropped columns on a partitioned table, you will get a record for each existing partition of that same table.

Wrap up

In this article, we saw the output of the contents of a page using `DBCC PAGE` with a table format and how that could let us use T-SQL to filter records, like if we have dropped columns.

We then saw an easier way to find our dropped columns by using some undocumented system views.

In my next article, I will share how we can have an idea of how much space our dropped columns are still holding so you can decide how quickly you want to schedule some maintenance tasks to recover that space.

Thanks for reading