SQL – Größe aller Tabellen einer Datenbank

Beim stöbern in meinen SQL Skripten bin ich gerade wieder auf das hier nachfolgend aufgeführte Skript zur Ausgabe der “Größe aller Tabellen einer Datenbank” gestoßen.

Da ich sicherlich nicht der einzige bin, der diese Informationen einer Datenbank hin und wieder benötigt, veröffentliche ich hier das Skript mit der Hoffnung dass es jemand gebrauchen kann.

SET NOCOUNT ON
CREATE TABLE #TableSpace 
(
	Rows int, 
	DataSpaceUsed int, 
	IndexSpaceUsed int
)
DECLARE @TableSpace table
(
	TableName varchar(255),
	Rows int, 
	DataSpaceUsed int, 
	IndexSpaceUsed int
)
DECLARE @Rows int, @DataSpaceUsed int, @IndexSpaceUsed int
DECLARE @TableName varchar(255)
DECLARE Table_Cursor CURSOR FOR
SELECT	user_name(o.uid) + '.' + o.name AS table_name
FROM	dbo.sysobjects o, dbo.sysindexes i 
WHERE	OBJECTPROPERTY(o.id, N'IsTable') = 1 
	AND i.id = o.id 
	AND i.indid < 2 
	AND o.name NOT LIKE N'#%' 
	AND xtype = 'U'
ORDER BY 1
OPEN Table_Cursor
---------------------------------
--Set Data
FETCH NEXT FROM Table_Cursor INTO @TableName
INSERT INTO #TableSpace (Rows, DataSpaceUsed, IndexSpaceUsed)
EXEC sp_MStablespace @TableName
SELECT	@Rows = Rows, 
	@DataSpaceUsed = DataSpaceUsed,
	@IndexSpaceUsed = IndexSpaceUsed
FROM	#TableSpace
INSERT INTO @TableSpace (TableName, Rows, DataSpaceUsed, IndexSpaceUsed)
VALUES (@TableName, @Rows, @DataSpaceUsed, @IndexSpaceUsed)
DELETE FROM #TableSpace
--------------------------------
WHILE @@FETCH_STATUS = 0


    BEGIN
    	---------------------------------
    	--Set Data
    	FETCH NEXT FROM Table_Cursor INTO @TableName
    	
    	INSERT INTO #TableSpace (Rows, DataSpaceUsed, IndexSpaceUsed)
    	EXEC sp_MStablespace @TableName
    	
    	SELECT	@Rows = Rows, 
    		@DataSpaceUsed = DataSpaceUsed,
    		@IndexSpaceUsed = IndexSpaceUsed
    	FROM	#TableSpace
    	
    	INSERT INTO @TableSpace (TableName, Rows, DataSpaceUsed, IndexSpaceUsed)
    	VALUES (@TableName, @Rows, @DataSpaceUsed, @IndexSpaceUsed)
    	
    	DELETE FROM #TableSpace
    	--------------------------------
END

CLOSE Table_Cursor
DEALLOCATE Table_Cursor
DROP TABLE #TableSpace
SELECT * 
FROM @TableSpace
ORDER BY Rows DESC

Über die Herkunft dieses Skripts (ich weiß dass ich zumindest die Idee von irgendwoher hatte) bin ich mir nicht mehr sicher. Eine gerade durchgeführte Recherche im Internet hat mir auch keinen direkten Aufschluss gegeben woher ich das Skript oder die Idee dazu hatte.

Sollte also jemand die Herkunft kennen, bitte einfach per Kommentar posten.

6 Gedanken zu „SQL – Größe aller Tabellen einer Datenbank“

  1. Hi,

    Herkunft kenne ich auch nicht. Aber habe ein ähnliches Script im Einsatz. Es bietet sich immer an, vor der Ermittlung der Größe noch ein DBCC updateusage() für alle Tabellen auszuführen, denn die Kenntnis über genutzte Pages im Server sind nicht immer synchron mit dem wirklichen Zustand..

  2. Hallo Daniel,
    danke für den Hinweis!
    Das bedeutet dann doch aber dass ein einfaches
    DBCC updateusage(0)
    am Anfang der Abfrage genügt um die Daten aller Tabellen für die aktuelle Datenbank zu aktualisieren.

  3. Thomas,
    das wäre eine Mögliche Quelle, obwohl dort im Forum auch davon gesprochen wurde, dass jemand die SP gefunden hat. Aber immerhin ist das eine Spur. Ich wollte auch nur klar stellen, dass ich die Abfrage hier nicht als meine eigene Erfindungen ausgebe und damit dem eigentlichen "Erfinder" auf den Schlips trete.
    Danke für den Hinweis

  4. Hi,

    ich persönlich finde folgende Abwandlung besser:
    Die Procedure "sp_MStablespace" ist nur inoffiziell von MS released – kann also irgendwann wegfallen.

    SET NOCOUNT ON

    DECLARE @TableSpace table
    (
    TableName varchar(255),
    Rows varchar(50),
    Reserved varchar(50),
    DataSpaceUsed varchar(50),
    IndexSpaceUsed varchar(50),
    Unused varchar(50)
    )
    DECLARE @TableName varchar(255)
    DECLARE Table_Cursor CURSOR FOR
    SELECT user_name(o.uid) + ‚.‘ + o.name AS table_name
    FROM dbo.sysobjects o, dbo.sysindexes i
    WHERE OBJECTPROPERTY(o.id, N’IsTable‘) = 1
    AND i.id = o.id
    AND i.indid < 2
    AND o.name NOT LIKE N’#%‘
    AND xtype = ‚U‘
    ORDER BY 1
    OPEN Table_Cursor
    ———————————

    FETCH NEXT FROM Table_Cursor INTO @TableName

    ——————————–
    WHILE @@FETCH_STATUS = 0

    BEGIN
    ———————————

    INSERT INTO @TableSpace (TableName, Rows, Reserved,DataSpaceUsed, IndexSpaceUsed,Unused)
    EXEC sp_spaceused @TableName

    FETCH NEXT FROM Table_Cursor INTO @TableName

    ——————————–
    END

    CLOSE Table_Cursor
    DEALLOCATE Table_Cursor
    SELECT *
    FROM @TableSpace
    ORDER BY 1 DESC

    Gutes Gelingen damit!

  5. Ein kleiner Hinweis: Ab SQL 2005 sollte statt
    SELECT user_name(o.uid) + ‘.’ + o.name AS table_name
    lieber
    SELECT SCHEMA_NAME(o.uid) + ‘.’ + o.name AS table_name
    genutzt werden, da ab SQL 2005 Schema und Benutzer voneinander getrennt wurden.

    Besser ist dann aber sowieso die Nutzung von sp_MSforeachtable wie in folgendem Script:

    CREATE TABLE #tmpTableSizes
    (
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
    )
    insert #tmpTableSizes
    EXEC sp_MSforeachtable @command1=“EXEC sp_spaceused ‚?'“

    select * from #tmpTableSizes
    order by cast(LEFT(reservedSize, LEN(reservedSize) – 4) as int) desc

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.