tsql – zeilenanzahl aller tabellen

es gibt viele unterschiedliche wege, die zeilenanzahl aller tabellen in einer datenbank einzeigen zu lassen. gerade wenn man noch anfaenger ist, landet man erstmal bei dem altbekannten:

SELECT COUNT(*) FROM TABELLE

…was man dann fuer jede tabelle machen muss. schnell hat man sich einen cursor drumherum gebaut damit das fuer alle tabellen in einer datenbank macht.

-- BEISPIEL 1

DECLARE @tables TABLE (
	tablename	varchar(255),
	rowcounter	int
)

DECLARE @table_name varchar(255)
DECLARE @SQLSTATEMENT nvarchar(255)
DECLARE @x int

DECLARE cur CURSOR FOR
	SELECT table_name 
	FROM information_schema.tables 
	WHERE TABLE_TYPE = 'BASE TABLE'
OPEN cur
FETCH FROM cur INTO @table_name
WHILE @@fetch_status = 0
BEGIN
	SET @SQLSTATEMENT = 
	 N'SELECT @x = COUNT(*) FROM ' + @table_name
	EXEC sp_executesql
		@SQLSTATEMENT,
		N'@x int OUTPUT',
		@x OUTPUT

	INSERT INTO @tables (tablename, rowcounter) 
	VALUES (@table_name, @x)
	FETCH FROM cur INTO @table_name
END
CLOSE cur
DEALLOCATE cur
SELECT * FROM @tables ORDER BY tablename

das ist natuerlich viel code fuer das bischen abfrage. es geht naemlich auch kuerzer mit der (von microsoft undokumentierten) sql-server internen stored procedure sp_MSforeachtable

-- BEISPIEL 2

CREATE TABLE #TABLEROWCOUNT
(
 TABLENAME sysname,
 ROWCOUNTER int
)
EXEC sp_MSforeachtable 
'INSERT #TABLEROWCOUNT (TABLENAME,ROWCOUNTER) 
SELECT ''?'',Count(*) from ?'
SELECT * FROM #TABLEROWCOUNT
DROP TABLE #TABLEROWCOUNT

schick ist das aber immer noch nicht. eleganter und performanter und ausfuehrlicher gehts mit einer (auch in sql server inegrierten) stored procedure namens sp_spaceused:

-- BEISPIEL 3

DECLARE @tables TABLE (
	tablename	varchar(255),
	rowcounter	int,
	reserved	varchar(255),
	data		varchar(255),
	index_size	varchar(255),
	unused		varchar(255)
)

DECLARE @table_name varchar(255)
DECLARE cur CURSOR FOR
	SELECT table_name 
	FROM information_schema.tables 
	WHERE TABLE_TYPE = 'BASE TABLE'
OPEN cur
FETCH FROM cur INTO @table_name
WHILE @@fetch_status = 0
BEGIN
	INSERT INTO @tables
	(tablename, rowcounter, reserved,
	data, index_size, unused)
	EXEC sp_spaceused @table_name
	FETCH FROM cur INTO @table_name
END
CLOSE cur
DEALLOCATE cur
SELECT * FROM @tables ORDER BY tablename

das schoenste und auch schnellste statement ist dieses hier (funktioniert erst ab sql server 2005):

-- BEISPIEL 4
-- zeigt alle user tables und deren zeilenanzahl der aktuellen 
-- datenbank. ohne "IS_MS_SHIPPED = 0" in der where 
-- klausel werden noch die system objekte angezeigt. 

SELECT T2.NAME, T3.ROW_COUNT 
FROM SYS.INDEXES T1 
INNER JOIN SYS.OBJECTS T2 ON T1.OBJECT_ID = T2.OBJECT_ID 
INNER JOIN SYS.DM_DB_PARTITION_STATS T3 ON 
T1.OBJECT_ID = T3.OBJECT_ID AND T1.INDEX_ID = T3.INDEX_ID
WHERE T1.INDEX_ID < 2 
AND T2.IS_MS_SHIPPED = 0 
ORDER BY T2.NAME

wenn man sich die performance der vier beispiele betrachtet wird schnell klar, welches man fuer groessere datenbanken nicht mehr gebrauchen kann. ich hatte eine datenbank zum "ausprobieren", in der 475 millionen datensaetze auf 60 tabellen verteilt waren. das waren die laufzeiten in millisekunden:

BEISPIEL 1: 156183 ms ( ~ 2 minuten 60 sekunden)
BEISPIEL 2: 155573 ms ( ~ 2 minuten 59 sekunden)
BEISPIEL 3: 173 ms
BEISPIEL 4: 13 ms

ich denke, dass es zu den ergebnissen nicht viel zu sagen gibt.