T-SQl script to check the largest index\table size
Question
Monday, September 16, 2013 10:09 PM
Hi All,
T-SQl script to check the largest index\table size in a database in sql server 2012
All replies (7)
Monday, September 16, 2013 10:46 PM ✅Answered | 2 votes
Try this for T-SQL script change the database name of course! ;)
USE <databasename>;
GO
SET NOCOUNT ON DBCC UPDATEUSAGE(0) -- DB size.
EXEC sp_spaceused-- Table row counts and sizes.
CREATE TABLE #t (
[name] NVARCHAR(128)
, [rows] CHAR(11)
, reserved VARCHAR(18)
, data VARCHAR(18)
, index_size VARCHAR(18)
, unused VARCHAR(18)) INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' SELECT * FROM #t-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]FROM #t
DROP TABLE #t;
Please click the Mark as answer button, and vote as helpful if this reply helps you.
Thanks!
Monday, September 16, 2013 11:07 PM ✅Answered
For table size:
http://therightstuff.de/CommentView,guid,df930155-f60f-4f56-ab33-f1352ff091a1.aspx
For index size:
SELECT
i.name AS IndexName,
SUM(s.used_page_count) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.[object_id] = object_id('dbo.TableName')
GROUP BY i.name
ORDER BY i.name
--OR
SELECT
i.name AS IndexName,
SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
db_id(), object_id('dbo.TableName'), 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
Regards,
André César Rodrigues
Please click the Mark as answer button and vote as helpful if this reply solves your problem. Thanks!
Blog: http://sqlmagu.blogspot.com.br LinkedIn:
Wednesday, September 18, 2013 7:05 AM ✅Answered
Hi Boma,
Please refer to the link below and hope it helpful.
SQL-Server-Scripts / TSQL / Find largest sql table or index in database.sql
https://github.com/Cecildt/SQL-Server-Scripts/blob/master/TSQL/Find%20largest%20sql%20table%20or%20index%20in%20database.sql
Thanks
Candy Zhou
Monday, September 16, 2013 10:32 PM
Please have a look at this link:
Determing SQL Server Table Size
another way is to use SSMS and right click on the database then reports... like follow figure:
The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd
Saeid Hasani's home page
Tuesday, September 17, 2013 3:10 AM
Note to remember , "sp_msForEachTable" is an undocumented procedure , and MS can be removed or modified without any notice ( usually they wont do).
http://weblogs.sqlteam.com/joew/archive/2007/10/23/60383.aspx
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
Wednesday, September 18, 2013 6:49 PM
Try this it's using sys.dm_db_partition_stats and is a good rough estimate. I don't think this sys table is dynamically updated whenever a change happens but don't quote me on that:
SELECT name = object_schema_name(object_id) + '.' + object_name(object_id)
, row_count
, data_size = 8*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)
, index_size = 8*(sum(used_page_count) - 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))
FROM
sys.dm_db_partition_stats
GROUP BY object_id, row_count
ORDER BY data_size, index_size DESC
.
.
.
If you are using 2008 you can also right click the database and select Disk Usage by Top Tables under Reports --> Standard Reports.
Friday, January 3, 2014 11:17 PM
Works like charm ! Thanks !
Don't be afraid people, my business is only about 1 or 0.