Find Database size in SQL Server

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

-- Script to find database sizes in MB, GB, and TB
SET NOCOUNT ON;

-- Create a temp table to store results
IF OBJECT_ID('tempdb..#DatabaseSizes') IS NOT NULL DROP TABLE #DatabaseSizes;

CREATE TABLE #DatabaseSizes (
    DatabaseName SYSNAME,
    SizeMB DECIMAL(18,2),
    SizeGB DECIMAL(18,2),
    SizeTB DECIMAL(18,4)
);

-- Insert size data for each database
EXEC sp_msforeachdb '
USE [?];
IF DB_ID() NOT IN (1,2,3,4) -- skip system databases
BEGIN
    INSERT INTO #DatabaseSizes (DatabaseName, SizeMB, SizeGB, SizeTB)
    SELECT
        DB_NAME() AS DatabaseName,
        SUM(size) * 8.0 / 1024 AS SizeMB,
        SUM(size) * 8.0 / 1024 / 1024 AS SizeGB,
        SUM(size) * 8.0 / 1024 / 1024 / 1024 AS SizeTB
    FROM sys.master_files
    WHERE DB_NAME(database_id) = DB_NAME();
END
';

-- Select the final result
SELECT * FROM #DatabaseSizes ORDER BY SizeMB DESC;

-- Clean up
DROP TABLE #DatabaseSizes;

SQL

Script 02

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

-- Retrieve file information for all user databases with size in MB, GB, and TB
SELECT
    DB_NAME(database_id) AS DatabaseName,            -- Name of the database
    name AS LogicalName,                             -- Logical name of the file
    physical_name AS PhysicalName,                   -- Physical path of the file
    (size * 8.0) / 1024 AS SizeMB,                   -- File size in MB
    (size * 8.0) / 1024 / 1024 AS SizeGB,            -- File size in GB
    (size * 8.0) / 1024 / 1024 / 1024 AS SizeTB      -- File size in TB
FROM sys.master_files
WHERE database_id > 4                                -- Exclude system databases: master, tempdb, model, msdb
ORDER BY DatabaseName, LogicalName;

SQL

Script 03

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

-- Retrieve file information for all user databases with size in MB, GB, and TB
SELECT
    DB_NAME(database_id) AS DatabaseName,           -- Database name
    name AS Logical_Name,                           -- Logical file name
    physical_name AS Physical_Name,                 -- Physical file path
    (size * 8.0) / 1024 AS SizeMB,                  -- Size in MB
    (size * 8.0) / 1024 / 1024 AS SizeGB,           -- Size in GB
    (size * 8.0) / 1024 / 1024 / 1024 AS SizeTB     -- Size in TB
FROM sys.master_files
WHERE database_id > 4                               -- Exclude system databases
ORDER BY DatabaseName, Logical_Name;