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.
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..
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.
Quelle könnte da sein:
http://www.ms-office-forum.net/forum/showthread.php?t=198725
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
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!
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