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
