CODEWITHSUNDEEP
sqlsql-server
bilal_izloo
bilal_izloo

Reputation: 37

Table size in the database

I am new to SQL i know how to get the size of the database using EXEC sp_spaceused I have a question how can i get the size of a table in Sql Server database.

like Employee Table in the database.

Thanks In advance.

Upvotes: 0

Views: 203

Answers (3)

marc_s
marc_s

Reputation: 754488

This slightly involved T-SQL statement will give you the exact same information that sp_spaceused returns:

WITH pages AS 
(
    SELECT
        object_id,
        SUM(reserved_page_count) AS reserved_pages,
        SUM(used_page_count) AS used_pages,
        SUM(CASE 
               WHEN (index_id < 2)
                  THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
               ELSE lob_used_page_count + row_overflow_used_page_count
            END) AS pages
    FROM
        sys.dm_db_partition_stats
    GROUP BY
        object_id),
extra AS 
(
    SELECT
        p.object_id,
        SUM(reserved_page_count) AS reserved_pages,
        SUM(used_page_count) AS used_pages
    FROM
        sys.dm_db_partition_stats p
    INNER JOIN 
        sys.internal_tables it ON p.object_id = it.object_id
    WHERE
        it.internal_type IN (202, 204, 211, 212, 213, 214, 215, 216)
    GROUP BY
        p.object_id)
SELECT
    SchemaName = s.NAME,
    TableName = t.NAME,
    CompleteName = s.NAME + '.' + t.NAME,
    (p.reserved_pages + ISNULL(e.reserved_pages, 0)) * 8 AS reserved_kb,
    pages * 8 AS data_kb ,
    (CASE 
        WHEN p.used_pages + ISNULL(e.used_pages, 0) > pages
          THEN (p.used_pages + ISNULL(e.used_pages, 0) - pages)
          ELSE 0
     END) * 8 AS index_kb ,
    (CASE 
        WHEN p.reserved_pages + ISNULL(e.reserved_pages, 0) > p.used_pages + ISNULL(e.used_pages, 0)
          THEN (p.reserved_pages + ISNULL(e.reserved_pages, 0) - p.used_pages + ISNULL(e.used_pages, 0))
          ELSE 0
     END) * 8 AS unused_kb
FROM
    pages p
INNER JOIN 
    sys.tables t ON t.object_id = p.object_id
INNER JOIN 
    sys.schemas s ON s.schema_id = t.schema_id
LEFT OUTER JOIN 
    extra e ON p.object_id = e.object_id
WHERE
    t.is_ms_shipped = 0
ORDER BY
    s.Name, t.Name

Of course, ideally, you'd "hide" this in a nicely useable stored procedure of your own to make it available whenever you need it ...

Upvotes: 1

Navneet
Navneet

Reputation: 447

One way is to look at the sys.dm_db_index_physical_stats output.

The min_record_size_in_bytes, max_record_size_in_bytes and avg_record_size_in_bytes will give you the sizes you want.

If you want to see the size of a particular record, another way is to find the physical row location, see

SQL Server 2008: New (undocumented) physical row locator function.

You can then dump the row physical structure, see Anatomy of a Record.

Upvotes: 0

munsifali
munsifali

Reputation: 1732

in mysql You can use this query to show the size of a table:

SELECT table_name AS "Table", 
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"
AND table_name = "$TABLE_NAME";

in sql:

sp_spaceused can get you information on the disk space used by a table, indexed view, or the whole database.

USE MyDatabase; GO

EXEC sp_spaceused N'dbo.MyTable'; GO

use this for all tables at once:

USE MyDatabase; GO 

sp_msforeachtable 'EXEC sp_spaceused [?]' GO

Upvotes: 0

Related Questions