if exists ( select name from sys.tables where name='TableSizes')
drop table TableSizes
GO
create table TableSizes (name char(100),rows bigint,reserved char(100),data char(100),index_size char(100),unused char(100))
GO
if exists ( select name from sys.views where name='TableSizesView')
drop view TableSizesView
GO
create view TableSizesView 
as 
select name,rows 
,SUBSTRING(reserved, 0,CHARINDEX('KB',reserved,0)) ReservedKb
,SUBSTRING(data, 0,CHARINDEX('KB',data,0)) DataKb
,SUBSTRING(index_size, 0,CHARINDEX('KB',index_size,0)) IndexSizeKb
,SUBSTRING(unused, 0,CHARINDEX('KB',unused,0)) UnusedKb
from TableSizes
GO


Declare @TableName  varchar(max)					   
DECLARE @SQLStatement varchar(max)					   
DECLARE GetIndexCursor CURSOR	                                   												
	FOR select name from sys.tables
OPEN GetIndexCursor		      				   												
FETCH NEXT FROM GetIndexCursor into @TableName  
																		
WHILE @@FETCH_STATUS = 0						           												
BEGIN 								   	   												
    set @SQLStatement='insert into TableSizes exec sp_spaceused '+ @TableName
	print (@SQLStatement)
	exec (@SQLStatement)
	FETCH NEXT FROM GetIndexCursor into @TableName  
 END 					   				   												
CLOSE GetIndexCursor					            												
DEALLOCATE GetIndexCursor	
GO
select * from TableSizesView order by rows desc
GO
