SQL queries the disk space occupied by each table in a database and shrinks the MDF file of the database.

 

Check the space occupied by most tables in a database

-- Query the space occupied by multiple tables in the database

Create Table TMP
(Name varchar (50 ),
Rows int,
Reserved varchar (50 ),
Data varchar (50 ),
Index_size varchar (50 ),
Unused varchar (50 ))
Go

Insert into TMP (name, rows, reserved, Data, index_size, unused)
Exec sp_msforeachtable @ command1 = "sp_spaceused '? '"
Select *, convert (INT, replace (data, 'kb', '') as TT from TMP order by TT DESC
Drop table TMP

Go

--Shrinking can reduce the database size, especially MDF files.
Alter DatabaseDbname
SetRecovery simple
Go 

DBCCShrinkdatabase (dbname)
Go 

Alter DatabaseDbname
SetRecoveryFull 
Go