T-SQL Query to find size of all tables in a database | SansSQL

Monday, July 22, 2013

T-SQL Query to find size of all tables in a database

Here is an T-SQL Query to find size of all tables in a database

DECLARE  @TableSize TABLE 
 (name nvarchar(150)
 ,[rows] int
 ,reserved nvarchar(150)
 ,data nvarchar(150)
 ,index_size nvarchar(150)
 ,unused nvarchar(150))

INSERT INTO @TableSize
EXEC sp_MSforeachtable @command1='EXEC sp_spaceused ''?'''

SELECT name AS TableName, 
       cast(replace(data, ' KB','') AS int)/1024 AS [TableDataSize(MB)]
FROM @TableSize
ORDER BY cast(replace(data, ' KB','') AS int) DESC

No comments:

Post a Comment

Ads