In a prior posting, I demonstrated how the undocumented sp_MSforeachtable stored procedure can be used to iterate through each of the tables within a database.

In a separate posting earlier this year, I also demonstrated how the DBCC CLEANTABLE command can be employed to reclaim space that was once consumed by variable length character columns that have been subsequently dropped.

In this post, I'd like to combine the two topics and show how the sp_MSforeachtable stored procedure command may be used in conjunction with the DBCC CLEANTABLE command to reclaim space from all tables after one or more columns were dropped.

This technique can be quite handy if you've recently dropped variable length character columns from many or all of your table.

The following script demonstrates this.

USE AdventureWorks ;
GO 

--create a test table CREATE TABLE dbo.Test
        (
         col1 INT         ,col2 VARCHAR(50)
        ,col3 VARCHAR(MAX)
        ) ; 

--create some test data DECLARE @cnt INT ;
SET @cnt = 0 ;
WHILE 1 = 1
        BEGIN                 
		SELECT @cnt = @cnt + 1 ;
		INSERT 
                         dbo.Test ( col1,col2,col3 )
		VALUES  (
                         @cnt
                        ,'dbo.Test row # ' 
			+ CAST(@cnt AS VARCHAR(100))
                        ,REPLICATE('A',@cnt)
                        ) ;
                IF @cnt = 1000 
                        BREAK ;
        END 



--view the table 
SELECT 
	* 
FROM
 	dbo.Test ;


 --check the size of the table 
SELECT         
	 alloc_unit_type_desc
	,page_count
	,avg_page_space_used_in_percent
	,record_count 
FROM
	sys.dm_db_index_physical_stats(
		  DB_ID()
		 ,OBJECT_ID(N'dbo.Test')
            	 ,NULL
	 	 ,NULL
		,'Detailed') ; 



--drop the last column 
ALTER TABLE dbo.Test DROP COLUMN col3 ; 


--check the space used again
SELECT         
	  alloc_unit_type_desc
	 ,page_count
	 ,avg_page_space_used_in_percent
	 ,record_count
FROM
	  sys.dm_db_index_physical_stats(
	 	  DB_ID()
		 ,OBJECT_ID(N'dbo.Test')
            	 ,NULL
		 ,NULL
		 ,'Detailed') ; 



--reclaim the space from the table 
EXEC sp_msforeachtable 'DBCC CLEANTABLE(0, ''?'') '; 



--check the space used one more time 
SELECT
	  alloc_unit_type_desc
	 ,page_count
	 ,avg_page_space_used_in_percent
	 ,record_count 
FROM
	 sys.dm_db_index_physical_stats(
	 	  DB_ID()
		 ,OBJECT_ID(N'dbo.Test')
            	 ,NULL
		 ,NULL
		 ,'Detailed') ; 



--clean up
DROP TABLE dbo.Test ; 

For details on each of the steps, please refer back to the original posts referenced above.

This is not something that you should do as part of your nightly, weekly, or even monthly maintenance plans. It should only be used as needed when the definition of one or more tables has changed.

Cheers!

Joe