SQL Server – Wie groß sind die einzelnen Tabellen?

Ich habe mich scho öfters gefragt, wie groß einzelne Tabellen in einer SQL Server Datenbank sind. Klar, es gibt viele Wege nach Rom, aber folgendes Script zeigt auch gleich die Anzahl der Zeilen an.

CREATE PROCEDURE dbo.allTables_SpaceUsed AS BEGIN SET NOCOUNT ON DBCC UPDATEUSAGE(0) CREATE TABLE #t ( id INT, TableName VARCHAR(32), NRows INT, Reserved FLOAT, TableSize FLOAT, IndexSize FLOAT, FreeSpace FLOAT ) INSERT #t EXEC sp_msForEachTable 'SELECT OBJECT_ID(PARSENAME(''?'',1)), PARSENAME(''?'',1), COUNT(),0,0,0,0 FROM ?' DECLARE @low INT SELECT @low = [low] FROM master.dbo.spt_values WHERE number = 1 AND type = 'E' UPDATE #t SET Reserved = x.r, IndexSize = x.i FROM (SELECT id, r = SUM(si.reserved), i = SUM(si.used) FROM sysindexes si WHERE si.indid IN (0, 1, 255) GROUP BY id) x WHERE x.id = #t.id UPDATE #t SET TableSize = (SELECT SUM(si.dpages) FROM sysindexes si WHERE si.indid < 2 AND si.id = #t.id) UPDATE #t SET TableSize = TableSize + (SELECT COALESCE(SUM(used), 0) FROM sysindexes si WHERE si.indid = 255 AND si.id = #t.id) UPDATE #t SET FreeSpace = Reserved - IndexSize UPDATE #t SET IndexSize = IndexSize - TableSize SELECT tablename, nrows, Reserved = LTRIM(STR( reserved @low / 1024.,15,0) + ' ' + 'KB'), DataSize = LTRIM(STR( tablesize @low / 1024.,15,0) + ' ' + 'KB'), IndexSize = LTRIM(STR( indexSize @low / 1024.,15,0) + ' ' + 'KB'), FreeSpace = LTRIM(STR( freeSpace * @low / 1024.,15,0) + ' ' + 'KB') FROM #t ORDER BY DataSize DROP TABLE #t END GO EXEC dbo.allTables_SpaceUsed

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /white-space: pre;/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

Führt man das Script z.B. im Microsoft SQL Server Management Studio aus, erhält man folgende Ausgabe.

image

Search
Categories
Other Posts this Month
Alltagszähler / Everyday Counter
Download the Everyday Counter