Query the table space and available space.
Set nocount on
Exec sp_msforeachtable
@ Precommand = N'
Create Table ##(
Id int identity,
Table Name sysname,
Number of fields int,
Number of records int,
Reserved space varchar (20 ),
Use space varchar (20 ),
Index space varchar (20 ),
Unused space varchar (20 ))',
@ Command1 = n' insert ## (Table Name, number of records, reserved space, space used, index space used, unused space) exec sp_spaceused ''? ''
Update ## set field COUNT = (select count (*) from syscolumns where id = object_id (''? '') Where id = scope_identity ()',
@ Postcommand = n' Select Table Name, number of records, replace (Reserved space, ''kb '', ''')/1024 allocated space MB, replace (used space, ''kb '', ''')/1024 data usage space MB
, Replace (index space, ''kb'', ''')/1024 index space MB, replace (unused space, ''kb '', '''')/1024 unused space MB from # order by number of records DESC drop table ##'
Set nocount off
