SQL – Größe aller Tabellen einer Datenbank

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

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..

   Antworten

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.

   Antworten

3. Quelle könnte da sein:

   http://www.ms-office-forum.net/forum/showthread.php?t=198725

   Antworten

4. 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

   Antworten

5. 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!

   Antworten

6. 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

   Antworten