How to get tablesize- SQL Server


Many times we need to get Size of tables and rows for each table for maintenance purpose.
Here I have given a script which will give
size information for each table using sp_spaceused procedure.



CREATE TABLE #tablesize_temp (

name VARCHAR(512),

ROWS INT,

reserved VARCHAR(51),

data VARCHAR(51),

index_size VARCHAR(51),

unused VARCHAR(51))


CREATE TABLE #tablesize (

name VARCHAR(512),

ROWS INT,

reserved BIGINT,

data BIGINT,

index_size BIGINT,

unused BIGINT)


INSERT INTO #tablesize_temp

EXEC Sp_msforeachtable

'exec sp_spaceused ''?'''


INSERT INTO #tablesize

SELECT name,

ROWS,

Replace(reserved,'kb',''),

Replace(data,'kb',''),

Replace(index_size,'kb',''),

Replace(unused,'kb','')

FROM #tablesize_temp


SELECT *

FROM #tablesize

ORDER BY ROWS DESC

No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts