How to find dropped or ghosted columns in Azure SQL DB? In SQL Server, dropping a column is a metadata only change; the data remains in the index until the index is rebuilt.

On prem, I use system_internals_partition_columns to determine what indexes have this ghosted column so I can rebuild and reclaim the space. This internal table is not accessible in Azure.

Does anyone know of a dmv or system table in Azure with this information?

Below is an example of what I am talking about.  These rows are approx. 2000 bytes so you get 4 rows per page.  Dropping a column should make each page about 50% free space but doesn't.  Worse, new records are still added at 4/page rather than 8/page.

DROP TABLE IF EXISTS dbo.TEST

GO
CREATE TABLE dbo.TEST (ID INT IDENTITY PRIMARY KEY,
                       a CHAR(995),
                       b CHAR(995))


 DECLARE @i INT = 1
   WHILE @i &lt;= 4000
   BEGIN
      INSERT INTO dbo.Test
      VALUES(@i,@i)

      SET @i +=1
    END   

SELECT t.name, p.page_count, p.avg_fragmentation_in_percent, p.avg_page_space_used_in_percent
  FROM sys.tables t
  CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(),t.object_id,1,NULL,'SAMPLED') p
  WHERE t.name = 'TEST'

/* Result

name    page_count  avg_fragmentation_in_percent    avg_page_space_used_in_percent
TEST    1000    0.3 99.6540647393131


--*/
GO

ALTER TABLE dbo.TEST
DROP COLUMN a

GO

SELECT t.name, p.page_count, p.avg_fragmentation_in_percent, p.avg_page_space_used_in_percent
  FROM sys.tables t
  CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(),t.object_id,1,NULL,'SAMPLED') p
  WHERE t.name = 'TEST'

/* Result - No change from the dropped column

name    page_count  avg_fragmentation_in_percent    avg_page_space_used_in_percent
TEST    1000    0.3 99.6540647393131

--*/


 DECLARE @i INT = 1
   WHILE @i &lt;= 4000
   BEGIN
      INSERT INTO dbo.Test
      VALUES(@i)

      SET @i +=1
    END  


SELECT t.name, p.page_count, p.avg_fragmentation_in_percent, p.avg_page_space_used_in_percent
  FROM sys.tables t
  CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(),t.object_id,1,NULL,'SAMPLED') p
  WHERE t.name = 'TEST'

/* Result - Same amount of space taken despite rows being half the size

name    page_count  avg_fragmentation_in_percent    avg_page_space_used_in_percent
TEST    2000    0.35    99.6540647393131

--*/

--This is how I find columns that are dropped but still being added to the page on prem
--DOES NOT WORK IN AZURE SQL DB
--SELECT tables.name
--  FROM sys.partitions
-- INNER JOIN sys.system_internals_partition_columns ON partitions.partition_id = system_internals_partition_columns.partition_id
-- INNER JOIN sys.tables ON tables.object_id = partitions.object_id
-- WHERE is_dropped = 1



I've continued looking into this off and on, but between not finding anything online and Randi's comment below, this may not be possible.
Since Azure database is MSSQL server then you can execute the following command to find how many ghost process per database

SELECT sum(ghost_record_count) total_ghost_records, db_name(database_id) 
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
group by database_id
order by total_ghost_records desc


And if you are looking for more detailed report then you can execute the following command

SELECT
db_name(database_id),
object_name(object_id),
ghost_record_count,
version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(N'db_name'), OBJECT_ID(N'table_name'), NULL, NULL , 'DETAILED');


Looking for long term solution to start recording this data then you can check this post. This will create table that store ghost records in the long term, but I recommend you to create it on test environment first to see if this is what you actually want.