Wyszukiwanie zmiennych tabelarycznych w bazie danych

Zmienne tabelaryczne to powszechnie znany twór, które bardzo często powoduje problemy wydajnościowy i powinien być stosowany tylko i wyłącznie w wyjątkowych sytuacjach – przykładowo było to wspomniane w poście o zmianie trybu kompatybilności (https://pl.seequality.net/zmiana-compatibility-level/). Problem jest jednak dość szeroko opisany i przedyskutowany i w tym poście zakładam, że jesteśmy w sytuacji, kiedy chcemy sprawdzić, które procedury składowane korzystają ze zmiennych tabelarycznych. W tym poście skupimy się na znalezieniu najbardziej efektywnej metody.

Problem wydawałoby się, że jest błahy, natomiast w rzeczywistości może nastręczyć trochę problemów. Potencjalnych rozwiązań może być co najmniej kilka. Pierwszym pomysłem wydaje się skorzystanie z widoku systemowego oraz przygotowanie odpowiedniej klauzuli where. Można skorzystać między innymi z:

SELECT object_definition(object_id) as [Def]
FROM sys.objects 
WHERE type='P'

Warto przy okazji wspomnieć, że korzystanie z “ROUTINE_DEFINITION” może okazać się ryzykowne, ponieważ tekst procedury zostanie ograniczony do 4000 znaków.

SELECT ROUTINE_DEFINITION as [Def]
FROM INFORMATION_SCHEMA.ROUTINES
AND ROUTINE_TYPE='PROCEDURE'

W przypadku dużych procedur możemy pominąć ich część. Przykładowe zapytanie mogłoby wyglądać podobnie do poniższego:

SELECT object_definition(object_id) as [Def]
FROM sys.objects 
WHERE type='P'
AND object_definition(object_id) LIKE '%DECLARE%@[a-zA-Z]%TABLE%'
AND object_definition(object_id) NOT LIKE '%DECLARE%[a-zA-Z]%@[a-zA-Z]%[a-zA-Z]%TABLE%'

Zapytanie nie jest skończone, natomiast już w takiej postaci z moich obserwacji wynika, że jest ekstremalnie wolne w przypadku dużej ilości procedur oraz, aby uzyskać rezultaty dla wszystkich baz, musielibyśmy stworzyć pętle. Pojawia się również problem znaków białych i ich czyszczenia, więc najprawdopodobniej potrzebna byłaby jeszcze funkcja. Jako, że jest to operacja raczej jednorazowa tworzenie dodatkowych obiektów nie jest niczym pożądanym.
Inną metodą może być wykorzystanie na przykład “Notepad++” oraz funkcji szukania w pliku, natomiast tutaj musimy przygotować odpowiedni “regex” i na końcu ręcznie przygotować listę z wynikami. Dlatego też postanowiłem przygotować prostą aplikację, która zrobi wszystko za nas. Kod aplikacji można znaleźć na githubie pod adresem https://github.com/seequality/seequality_table_variables_finder Wszystko sprowadza się tak naprawdę do jednej metody i kilku linijek kodu:

foreach (var file in allFiles)
{
    var txt = File.ReadAllText(file);
    var _txt = Regex.Replace(txt, @"\s+", " ")
                            .ToLower()
                            .Replace(System.Environment.NewLine," ")
                            .Replace("\t", " ")
                            .Replace("\n", " ")
                            .Replace("\r", " ")
                            .Replace("  ", " ");

    var words = _txt.Split(' ');
    int n = words.Count();
                
    for (int i = 0; i < n; i++)
    {

        if (words[i] == "declare" && words[i+1].StartsWith("@") && words[i+2] == "table")
        {
            soutput.AppendLine(file + " > " + words[i + 1]);
            outputList.Add(new Stat() { FileName = file, VariableName = words[i + 1] });
        }
    }
}

Rezultatem będzie lista plików oraz lista zmiennych tabelarycznych znaleziona w tych plikach.

C:\GIT\database\database_name_1\schema\Stored Procedures\dbo.stored_procedure_1.sql > @table_variable_1
C:\GIT\database\database_name_1\schema\Stored Procedures\dbo.stored_procedure_2.sql > @table_variable_1
C:\GIT\database\database_name_1\schema\Stored Procedures\dbo.stored_procedure_3.sql > @table_variable_1
C:\GIT\database\database_name_2\schema\Stored Procedures\dbo.stored_procedure_4.sql > @table_variable_1, @table_variable_2, @table_variable_3
C:\GIT\database\database_name_2\schema\Stored Procedures\dbo.stored_procedure_5.sql > @table_variable_1, @table_variable_3

Oczywiście w tym przypadku musimy posiadać skrypt bazy danych zapisany na dysku jako repozytorium, ale przecież każdy z nas korzysta z systemu kontroli wersji (TFS, GIT, etc), więc nie powinna to być żadna przeszkoda. Jeżeli z jakichkolwiek powodów nie posiadamy skryptu bazy danych w systemie kontroli wersji możemy wykorzystać Visual Studio oraz “Database Project”. Wówczas możemy zaimportować kod bazy/baz danych do plików.

W moim przypadku C# mimo wszystko był najprostszą i najbardziej efektywną metodą. Być może Wy również skorzystacie z tego kodu. Wszystko dostępne na https://github.com/seequality/seequality_table_variables_finder