SQL SERVER – Query to Find ByteSize of All the Tables in Database

I Am writing here two ways to find the database table size

Method One 1:
---------------------------------------------------

SELECT CASE WHEN (GROUPING(sob.name)=1) THEN 'All_Tables'
ELSE ISNULL(sob.name, 'unknown') END AS Table_name,
SUM(sys.length) AS Byte_LengthFROM sysobjects sob, syscolumns sysWHERE sob.xtype='u' AND sys.id=sob.idGROUP BY sob.nameWITH CUBE

Method One 2:
---------------------------------------------------
Ever wonder how big a table really is in your database? You know there are a million rows in the table, but how much space is that really taking?

SQL Server provides a built-in stored procedure that you can run to easily show the size of a table, including the size of the indexes… which might surprise you.
Syntax:   sp_spaceused ‘Tablename’

Method One 3:
---------------------------------------------------
Actually SQL Server gives you everything you need with its Stored Procedure sp_spaceused. Unfortunately this SP does not support iterating over all tables in a database, so we needed to leverage another (undocumented) Stored Procedure sp_msForEachTable.

SET NOCOUNT ON 
DBCC UPDATEUSAGE(0) 
-- DB size.
EXEC sp_spaceused

-- Table row counts and sizes.
CREATE TABLE #t 
( 
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18), 
    data VARCHAR(18), 
    index_size VARCHAR(18),
    unused VARCHAR(18)
) 

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 

SELECT *
FROM   #t

-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM   #t
 
DROP TABLE #t 

Method One 4:
---------------------------------------------------

CREATE PROCEDURE GetAllTableSizes

AS

/*

Obtains spaced used data for ALL user tables in the database

*/

DECLARE @TableName VARCHAR(100) --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing

DECLARE tableCursor CURSOR

FOR

select [name]

from dbo.sysobjects

where OBJECTPROPERTY(id, N'IsUserTable') = 1

FOR READ ONLY

--A procedure level temp table to store the results

CREATE TABLE #TempTable

(

tableName varchar(100),

numberofRows varchar(100),

reservedSize varchar(50),

dataSize varchar(50),

indexSize varchar(50),

unusedSize varchar(50)

)

--Open the cursor

OPEN tableCursor

--Get the first table name from the cursor

FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch

WHILE (@@Fetch_Status >= 0)

BEGIN

--Dump the results of the sp_spaceused query to the temp table

INSERT #TempTable

EXEC sp_spaceused @TableName

--Get the next table name

FETCH NEXT FROM tableCursor INTO @TableName

END

--Get rid of the cursor

CLOSE tableCursor

DEALLOCATE tableCursor

--Select all records so we can use the reults

SELECT *

FROM #TempTable

--Final cleanup!

DROP TABLE #TempTable

GO

Exec GetAllTableSizes