Hi All,

Today we got one requirement to find the size of given tables in sql database

We have written the below script and its working fine

USE AdventureWorks2012

GO

SELECT t.name

AS

TableName,

p.rows

AS RowCounts,

Sum(((( Cast(a.used_pages AS DECIMAL(18, 4)) * 8192 ) / 1024 ) / 1024 )) AS ‘File Size (MB)’,

Sum(((( Cast(a.used_pages AS DECIMAL(18, 4)) * 8192 ) / 1024 ) / 1024 ) / 1024)AS ‘File Size (GB)’,

Sum((((( Cast(a.used_pages AS DECIMAL(18, 4)) * 8192 ) / 1024 ) / 1024 ) / 1024 / 1024 ))AS ‘File Size (TB)’

FROM sys.tables t

INNER JOIN sys.indexes i

ON t.object_id = i.object_id

INNER JOIN sys.partitions p

ON i.object_id = p.object_id

AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a

ON p.partition_id = a.container_id

WHERE t.name IN(‘ErrorLog’,’DatabaseLog’)

AND t.is_ms_shipped = 0

AND i.object_id > 255

GROUP BY t.name,

p.rows

ORDER BY t.name

Result:

We can get the size of tables in MB,GB’s and TB’s

Thanks for viewing this.

Regards,

Chaitanya

Visit site: http://www.sqlblogging.com

Send an Email: sqlblogging