Generowanie definicji widoku na bazie tabeli
Dzisiaj znów zajmę się procedurą, której wymyślenie narzuciła mi potrzeba. Zadanie polegało na utworzeniu wirtualnych widoków reprezentujących wszystkie tabele w bazie. Jednym z podejść jest ręczne utworzenie każdego widoku. Jeżeli jednak tych tabel jest więcej niż kilkanaście, wtedy przydaje się jakiś ogólniejszy skrypt. Jeżeli tabel jest kilkaset, wtedy jesteśmy wręcz zmuszeni do poszukania automatycznego rozwiązania. Przyjrzyjmy się zatem przykładowemu rozwiązaniu zaprezentowanemu poniżej:
CREATE TABLE Test1
(
Column1 int,
Column2 float,
Column3 varchar(12),
Column4 bit,
Column5 decimal(9,2)
)
--Test procedury
DECLARE @tabName nvarchar(128) = 'Test1'
DECLARE @columnlist nvarchar(MAX)
SELECT @columnlist = COALESCE(@columnlist+',' ,'') + C.name
FROM sys.tables T
JOIN sys.columns C ON C.[object_id] = T.[object_id]
WHERE T.name = @tabName
ORDER BY column_id
SELECT 'CREATE VIEW v' + @tabName + ' AS SELECT ' +
@columnlist + ' FROM ' + @tabName
Wynikiem działania powyższej procedury będzie taki oto ciąg znaków:
Automatyzacja zadania polegała już tylko na napisaniu pętli i wykonaniu skryptu dla każdej tabeli w bazie. Szerszym zastosowaniem jest dodanie warunku do każdej tabeli, która zawiera konkretną kolumnę.
Dodawanie warunku dla specyficznych kolumn widoku
Przypuśćmy teraz, że chcemy utworzyć widoki przefiltrowane. Wszystkie widoki, które mają kolumnę Column1, powinny zwracać tylko te rekordy, które mają tę wartość ustawioną na 1. Zastosowanie takiego podejścia może być bardzo szerokie. Filtrowana może być kolumna określająca status rekordu lub specyficzny stan (Usunięty, Nieaktywny, Aktywny, Zabezpieczony, WymagaPraw). Może to być pewien typ, a konkretny użytkownik powinien mieć dostęp do rekordów tylko tego jednego konkretnego typu. Przyjrzyjmy się zatem gotowemu rozwiązaniu:
DECLARE @filteredColumn nvarchar(128) = 'Column1'
DECLARE @columnlist nvarchar(MAX)
SELECT @columnlist = COALESCE(@columnlist+',' ,'') + C.name
FROM sys.tables T
JOIN sys.columns C ON C.[object_id] = T.[object_id]
WHERE T.name = @tabName
ORDER BY column_id
IF EXISTS(SELECT C.name
FROM sys.tables T
JOIN sys.columns C ON C.[object_id] = T.[object_id]
WHERE T.name = @tabName AND C.name = @filteredColumn)
SELECT 'CREATE VIEW v' + @tabName + ' AS SELECT ' +
@columnlist + ' FROM ' + @tabName +
' WHERE ' + @filteredColumn + '=1'
ELSE
SELECT 'CREATE VIEW v' + @tabName + ' AS SELECT ' +
@columnlist + ' FROM ' + @tabName
Wynikiem działania skryptu będzie taki oto ciąg znaków:
Jak to działa? Bardzo prosto. Tworzony jest standardowy widok, jak w poprzednim przykładzie, a następnie, w zależności od warunku EXISTS, dodawany jest filtr WHERE na kolumnie podanej w parametrze @filteredColumn.
Jak zwykle dobrym rozwiązaniem może się okazać stworzenie funkcji przyjmującej poszczególne parametry, tj.: nazwę tabeli, nazwę nowego widoku (jeżeli nie odpowiada nm nazewnictwo przedrostkowe z literą v), nazwa filtrowanej kolumny, warunek dołączany dla danej kolumny. Ciekaw jestem podobnych rozwiązań. Zachęcam do dzielenia się nimi w komentarzach.
Kategoria:SQL ServerWidoki
Brak komentarzy - bądź pierwszy