Wednesday, January 27, 2016

Get Total Rows and size for all tables in SQL Server


Here is the query to find total records for all tables along with size 


DECLARE @size VARCHAR(10)
DECLARE @dividedby INT


SET @size= 'MB'  -- change this to GB you'll get size in Gb otherwise  it will be in MBs

IF @size='GB'
SET @dividedby = 1048576

ELSE
BEGIN

SET @dividedby = 1024
SET @size='MB'

END


CREATE TABLE #temp (
TableName sysname ,
TotalRows BIGINT,
reserved_size VARCHAR(500),
data_size VARCHAR(500),
index_size VARCHAR(500),
unused_size VARCHAR(500))

SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.TableName,
a.TotalRows,

 CAST(ROUND((CAST(REPLACE(a.data_size, ' KB', '') AS DECIMAL(20,4))/@dividedby),4) AS NUMERIC(36,4)) 'Data size',
 CAST(ROUND((CAST(REPLACE(a.reserved_size, ' KB', '') AS DECIMAL(20,4))/@dividedby),4) AS NUMERIC(36,4)) 'Reserved size ',
 CAST(ROUND((CAST(REPLACE(a.index_size, ' KB', '') AS DECIMAL(20,4))/@dividedby),4) AS NUMERIC(36,4)) 'Index size',
 CAST(ROUND((CAST(REPLACE(a.unused_size, ' KB', '') AS DECIMAL(20,4))/@dividedby),4) AS NUMERIC(36,4)) 'Unused size ',
 @size 'size in '

FROM #temp a
ORDER BY  TotalRows DESC

DROP TABLE #temp



No comments:

Post a Comment