Get SQL table space – and store for KPI over time

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