HP's Blog - SQL - Größe aller Tabellen einer Datenbank

Wednesday, August 05, 2009

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.

Tips und Tricks | SQL Server | SQL

Wednesday, August 05, 2009 9:26:38 AM (W. Europe Daylight Time, UTC+02:00)

Comments [5]
Autor: Hans-Peter Schelian | Trackback

Wednesday, August 05, 2009 12:30:12 PM (W. Europe Daylight Time, UTC+02:00)

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

Daniel

Wednesday, August 05, 2009 5:31:33 PM (W. Europe Daylight Time, UTC+02:00)

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.

Hans-Peter Schelian

Thursday, August 06, 2009 2:30:02 PM (W. Europe Daylight Time, UTC+02:00)

Quelle könnte da sein:

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

Thomas

Thursday, August 06, 2009 8:14:02 PM (W. Europe Daylight Time, UTC+02:00)

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

Hans-Peter Schelian

Friday, November 27, 2009 10:58:36 AM (W. Europe Standard Time, UTC+01:00)

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!

felix

Name
E-mail	(will show your gravatar icon)
Home page

	Remember Me
Comment (Some html is allowed: `a@href@title, strike`) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.
Enter the code shown (prevents robots):
Live Comment Preview