July 20, 2011

Code to Get all table size in sql database server, code to get table size, get sql tables size, show table size in sql server

SET NOCOUNT ON DBCC UPDATEUSAGE(0) -- Database size.

Following code you can write in stored procedure or you can directly run on query browser to get all table size in selected database.

EXEC sp_spaceused-- Table row counts and sizes.

 CREATE TABLE #test (    
 [name] NVARCHAR(128),    
 [rows] CHAR(11),    
 reserved VARCHAR(18),   
 data VARCHAR(18),    
 index_size VARCHAR(18),   
 unused VARCHAR(18)) INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''  
 SELECT *FROM  #test-- # of rows.  
 SELECT SUM(CAST([rows] AS int)) AS [rows]FROM #test  
 DROP TABLE #test  

I hope this code will help you a lot on database administration.