Using the following query I can get the size of the indexes for a table:
SELECT
i.name AS IndexName,
SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(db_id(), object_id('schema.table'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name
If I execute sp_spaceused 'schema.table'
does the size without indexes correspond to data
column?
Any way to get the size of the table without indexes in sql server? (SELECT
statement would be nice as I'd like to plug this query into a bigger query).
If I execute Get size of all tables in database, the sizes for tables are almost the same as the sizes for indexes (suggesting that something is wrong).
Best Answer
There are multiple ways of finding size of a table in the database. First and easiest way is to right click on Table and check the storage tab and this gives you Data space and Index Space. Data space is nothing but size of data in the table and similarly index space gives you size of index in the mentioned table.
The above information would exactly match with the command
sp_spaceused 'schema_name.table_name'
We can cross check the same for the Users Table(in dbo schema and StackOverFlow Database).Here, if you add data, index size and unused(in KB), you will get reserved space by table.
You may also check the size of table and index by running command sp_blitzindex(from Mr. Brent Ozar). Command would be as below:
Here, you may check the Size column. Clustered index size will give you Table size and you can also find size of each and every index as well as their usage(if interested).
Moreover you can also check table size of all of them by getting into Reports as below:
You may also run below select statement to get full size and index size of all the tables:
If you want only the table sizes then execute the following:
Hope this helps.