Iterowanie przy pomocy ms_foreachtable i pętli WHILE

W naszej codziennej pracy z bazami danych bardzo często zdarza się sytuacja gdy musimy wykonać różnego rodzaju zadania związane ze sprawdzeniem większej ilości tabel. Na przykład chcemy sprawdzić ilość wierszy w tabelach co wymaga od nas napisania zapytania, które w jakiś sposób “przejrzy” większość bądź wszystkie tabele. Oczywiście możliwości rozwiązania takiego zadania jest naprawdę wiele: od ręcznego odpytywania każdej tabeli, aż po przygotowanie bardziej dynamicznego skryptu używając TSQL czy też Powershell. Dziś chciałbym przedstawić dwie możliwości podejścia do problemu czyli procedura ms_foreachtable oraz pętla WHILE. Bonusowo wspomnimy powiemy sobie parę słów o widoku sys.partitions, który daje nam pewne możliwości w tym temacie. Zapraszam do lektury!

Zaczniemy od pierwszej procedury, a więc ms_foreachtable. Jest to procedura wbudowana w SQL Server, która nie jest w żaden sposób udokumentowana, a co za tym idzie należy jej używać tylko w celach testowych lub w ramach ciekawostki. Microsoft nie zaleca używania nieudokumentowanych funkcjonalności i może takie obiekty usunąć w przyszłości bez żadnego ostrzeżenia co powinno dać nam do myślenia. Jej użycie sprowadza się do następującego zapisu:

sp_msforeachtable 'zapytanie'

Gdzie jako argument przekazujemy zapytanie – taka konstrukcja spowoduje wykonanie danego zapytania tyle razy ile jest tabel w bazie danych w której aktualnie się znajdujemy. Oczywiście cała “moc” tej konstrukcji tkwi w tym, że procedura działa jak pętla i w ramach zapytania możemy odwoływać się do aktualnie przetwarzanej tabeli. Zrobimy to w bardzo prosty sposób używając znaku zapytania “?”:

sp_msforeachtable 'SELECT COUNT(*) FROM ?'

Jak widać na rezultacie poszczególne zliczenia zostały wykonane poprawnie. Póki co nie wiemy, która liczba dotyczy której tabeli ale możemy to w bardzo prosty sposób poprawić:

sp_msforeachtable 'SELECT ''?'' AS TableName,COUNT(*) As NumberOfRows FROM ?'

Całkiem fajna funkcjonalność prawda? Bardzo prosta w użyciu pętla pozwalająca nam osiągnąć fajne rezultaty. Oczywiście wprawne oko zauważy, że każdy rezultat to osobny batch więc ciężko analizować te dane razem – jednak i na to jest sposób którym jest wrzucenie rezultatu do tabeli:

DROP TABLE IF EXISTS #tmpTablesStats

CREATE TABLE #tmpTablesStats
(
	Id			 INT IDENTITY,
	TableName	 VARCHAR(150),
	NumberOfRows BIGINT
);
GO

sp_msforeachtable '
INSERT INTO #tmpTablesStats (TableName,NumberOfRows)
SELECT ''?'' AS TableName,COUNT(*) As NumberOfRows FROM ?'
GO

SELECT * FROM #tmpTablesStats

Rezultat:

Jak już wspomniałem powyższa konstrukcja nie jest udokumentowana podobnie jak siostrzana ms_foreachdb iterująca po bazach na serwerze. Jak więc osiągnąć podobny efekt tylko za pomocą pełnoprawnych funkcjonalności? Pierwszą rzeczą na jaką chciałbym zwrócić waszą uwagę jest wspomniany we wstępie widok sys.partitions, który zawiera informację o tym ile wierszy znajduje się w poszczególnych partycjach, a co za tym idzie w tabelach. Oczywiście informacje tam zawarte bazują na statystykach i nie muszą być w 100% dokładne jednak w wielu przypadkach mogą okazać się wystarczające. Przykładowe zapytania w oparciu o sys.partitions wygląda następująco:

SELECT 
	 OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName
	,OBJECT_NAME(p.object_id) AS TableName
	,SUM(p.rows)
FROM sys.partitions AS p
JOIN sys.objects AS o
	ON o.object_id=p.object_id
WHERE 
	p.index_id IN (0,1)
	AND o.type='U'
GROUP BY 
	 OBJECT_SCHEMA_NAME(p.object_id)
	,OBJECT_NAME(p.object_id)

Założone przeze mnie filtry index_id równy 0 lub 1 oznacza, że będziemy patrzeć tylko na ilości wierszy w stertach oraz w indeksach klastrowanych czyli po prostu będziemy uwzględniać tabele same w sobie, a nie indeksy nieklastrowane. Type=’U’ to filtr, który oznacza, że interesują nas tabele użytkownika, a nie inne obiekty jak tabele systemowe itd. Rezultat tego zapytania przedstawia się w następujący sposób:

Otrzymaliśmy zatem poglądowe informacje ile wierszy zawiera jaka tabela odpytując jedynie metadata. Warto zwrócić uwagę, że często dane otrzymane w ten sposób są w 100% zgodne jednak wszystko zależy od statystyk. Polecam zapoznać się z dokumentacją zarówno sys.partitions jak i sys.objects bo znajduje się tam sporo przydatnych danych.

Co jeśli chcielibyśmy otrzymać dokładne rezultaty tak jak miało to miejsce w przypadku ms_foreachtable? Wystarczy użyć pętli, widoku systemowego sys.tables oraz dynamicznego SQLa:

DECLARE @Counter		INT =1,
		@NumberOfTables INT =(SELECT COUNT(*) FROM sys.tables),
		@CurrentQuery	NVARCHAR(500);

DROP TABLE IF EXISTS #QueriesToExecute;

SELECT 
	ROW_NUMBER() OVER(ORDER BY name) AS RN
,'SELECT 
''['+OBJECT_SCHEMA_NAME(object_id)+'].['+name+']'' AS TableName,
COUNT(*) AS NumberOfRows
FROM
['+OBJECT_SCHEMA_NAME(object_id)+'].['+name+']' AS Query
INTO #QueriesToExecute
FROM sys.tables


WHILE @Counter<=@NumberOfTables
BEGIN
	SET @CurrentQuery=(SELECT Query FROM #QueriesToExecute WHERE RN=@Counter )
	EXEC (@CurrentQuery)
	SET @Counter=@Counter+1
END

Co tu się podziało? Mamy trzy zmienne:

• @Counter to zwykły licznik,
• @NumberOfTables to liczba tabel w aktualnej tabeli wyciągnięta z sys.tables,
• @CurrenyQuery to będzie zapytanie do wykonania przy każdej iteracji pętli.

Mamy również zapytanie, które zbuduje nam zestaw zapytań ze zliczeniem dla każdej tabeli bazując oczywiście na sys.tables – zapytania te zostaną umieszczone w tabeli tymczasowej. Tak więc po wykonaniu w tabeli tymczasowej znajdziemy zapytania wyglądające np. tak:

SELECT   '[dbo].[DimAccount]' AS TableName,  
COUNT(*) AS NumberOfRows  
FROM  [dbo].[DimAccount]

Oczywiście jeśli potrzebujecie większej ilości informacji to można naszą konstrukcję zapytania odpowiednio zmodyfikować. Dla celów niniejszego artykułu istotne jest jedynie zliczenie liczby wierszy w tabelach dlatego zostawiamy to w tak prostej formie.

W tabeli tymczasowej dodałem również ROW_NUMBER po to żebyśmy mieli pewnego rodzaju indeks liczbowy po którym będziemy się poruszać. Następnie w pętli wskazujemy, że chcemy aby pętla się kręciła dopóki nasz licznik (@Counter) nie dojdzie do ostatniej tabeli (@NumberOfTables). Sama pętla raczej nie wymaga dodatkowego wytłumaczenia i polega na wyszukaniu w tabeli tymczasowej zapytania do wykonania i jego uruchomienia przy pomocy EXEC.

Całość mimo swojej prostoty działa zgodnie z oczekiwaniami:

Oczywiście jeśli chcielibyśmy te dane umieścić w pojedynczej tabeli to możemy to zrobić analogicznie jak robiliśmy to w przypadku ms_foreachtable. Dodatkowo używając tego podejścia mamy pełną kontrolę nad tym, które obiekty chcemy zliczać (na przykład wskazując nazwę schematu jako warunek filtrujący) co w przypadku ms_foreachtable jest utrudnione, a przy bardziej zaawansowanych filtracjach wręcz niemożliwe.

Jak widać zliczenie liczby wierszy we wszystkich tabelach jest bardzo proste, a sposobów na wykonanie tego zadania bardzo wiele. Ogólnie rzecz biorąc polecam używanie rozwiązań w pełni udokumentowanych, a więc bezpiecznych ze względu na brak oficjalnych informacji ms_foreachtable i ms_foreachdb powinniśmy traktować jedynie w ramach ciekawostki. Szczególną uwagę należy zwrócić na kod znaleziony w sieci ponieważ często używane są tam konstrukcje przestarzałe i/lub nieudokumentowane .To by było na tyle jeśli chodzi o dzisiejszy artykuł – pozdrawiam serdecznie!