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.
