Get SQL table space – and store for KPI over time
06-Jul-1212 Leave a comment
See http://therightstuff.de/CommentView,guid,df930155-f60f-4f56-ab33-f1352ff091a1.aspx
For a single table
EXEC sp_spaceused ‘<schema>.<tablename>’
For database size
–See sql file for dbo.TableSpaceUsed
SET NOCOUNT ON DBCC UPDATEUSAGE(0) — DB size.
EXEC sp_spaceused– Table row counts and sizes.
For all tables
See http://codebetter.com/raymondlewallen/2005/03/25/using-sp_msforeachtable/
http://wp.me/p17IS4-ge
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 ”?”’
–Optional, but I am now using a table I have created for storing this table size over time
DECLARE @SpoolID int SELECT @SpoolID =ISNULL((SELECTMAX(SpoolID)FROM dbo.TableSpaceUsed), 0)+ 1
–SELECT @SpoolID
INSERT INTO dbo.TableSpaceUsed (SpoolID, [name], [rows], reserved, reservedText, data, index_size, unused)
SELECT @SpoolID, name,rows,CAST(REPLACE(reserved,‘KB’,”)ASint)AS reserved, reserved AS reservedText, data, index_size, unused
FROM #t
SELECT * FROM #t ORDER BY CAST(REPLACE(reserved,‘KB’,”)AS int) DESC— # of rows.
SELECT SUM(CAST([rows] ASint))AS [rows] FROM #t
DROP TABLE #t