Introduction

In this article, we will try to pull all sizes from SQL server. How to find Database Size in SQL Server? How to Get size of all tables in the SQL Server database? We will use different SQL Queries to find Database file size, database size, and tables size. Very often we need to check the size of the Database whenever we plan to move our entire database or file or only tables from one server to another server. Using these queries above mention task will easier for us.

Script 01

To retrieve file size information for all databases, you can use the following TSQL code

SELECT 
    DatabaseName = DB_NAME(database_id), -- Database name
    LogSizeMB = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2)), -- Log file size in MB
    DataFileSizeMB = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2)), -- Data file size in MB
    TotalSizeMB = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)) -- Total size (combined log and data) in MB
FROM sys.master_files WITH(NOWAIT) -- Retrieve file information from sys.master_files
WHERE type_desc IN ('LOG', 'ROWS') -- Filter for log and data files
GROUP BY database_id -- Group the results by database_id
ORDER BY DatabaseName; -- Order the results by database name

Script 02

Here’s the T-SQL code to retrieve file information for all databases

-- Retrieve the file information for all databases
SELECT 
    DB_NAME(database_id) AS DatabaseName, -- Alias for the database name
    Name AS Logical_Name, -- Alias for the logical name of the file
    Physical_Name, -- Physical name of the file
    (size * 8) / 1024 AS SizeMB -- Calculate file size in MB
FROM sys.master_files
WHERE database_id > 4; -- Exclude system databases

GO -- Terminate the batch