SQL Server - Get Estimated Table Size

Question

In production, issuing SELECT COUNTcan be a bad idea - it can be a performance hit depending on your database engine. In Oracle, if I want to get an idea of ​​the size of a table without having to resort to COUNT, I can do the following:

SELECT
    table_name,
    num_rows,
    last_analyzed
FROM all_tables
WHERE table_name = 'MY_TABLE_NAME';

This will extract the Oracle table analyzes, if included. While the calculation is not accurate, it can give me an idea of ​​how big the table is if I need to query it (and the last_analysed column lets me know how old this approximation is).

How can I do something like this in SQL Server? (Bound - is it necessary for SQL Server? Oracle should count line by line, therefore, avoid it.)

Thank!

Answer 1

You can also use management studio.

Right Click on table -> Properties -> Storage

or you can use the query as follows:

sp_spaceused 'TableName'

To get it for all tables, you can use it as tihs:

CREATE TABLE #tmp
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)
insert #tmp
EXEC sp_MSforeachtable @cmd="EXEC sp_spaceused '?'"


select  * from #tmp

Answer 2

You can call sp_spaceused 'table_name'.

If you want to do this for all tables, wrap it inside sp_MSforeachtable:

sp_MSforeachtable 'sp_spaceused ''[?]'''

A call sp_spaceusedwithout any parameters will give you the size of the database.

Answer 3

: @marc_s

SELECT 
    s.Name AS SchemaName,
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%'    -- filter out system tables for diagramming
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    s.Name, t.Name

Answer 4

, ; SQL Server , dmv.

SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID('MY_TABLE_NAME')   
AND (index_id=0 or index_id=1);