Spis treści:

Kategoria:SQL ServerWidoki


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:

--Przygotowanie tabeli testowej
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.columnsON 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:

CREATE VIEW vTest1 AS SELECT Column1,Column2,Column3,Column4,Column5 FROM Test1

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 @tabName nvarchar(128) = 'Test1'
DECLARE @filteredColumn nvarchar(128) = 'Column1'
DECLARE @columnlist nvarchar(MAX)
SELECT @columnlist = COALESCE(@columnlist+',' ,'') + C.name
FROM sys.tables T
JOIN sys.columnsON 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.columnsON 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:

CREATE VIEW vTest1 AS SELECT Column1,Column2,Column3,Column4,Column5 FROM Test1 WHERE Column1=1

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

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
chcę dodać kolumnę, która będzie połączeniem dwóch innych istniejących już kolumn, jak powinien wyglądać scrypt?
Przydałyby się jeszcze 2 rzeczy do cz. 3 i byłoby superanckie.
1. Na starcie sortuje wg jakiejś kolumny i tam jest już strzałeczka. Widok takiej strzałeczki daje znać użytkownikowi, że taką tabele można sortować, a na razie pojawia się ona tylko po kliknięciu.
2. Uwzględnienie polskich znaków, bo np. przy sortowaniu Nazwisk i Imion jest to bardzo uciążliwe.
Ogólnie bardzo fajnie i prosto.
PS. Jest ten artykuł z jQuery już dostępny.
bardo ciekawe , można dzięki takim wpisom dostrzec wędkę..
Bardzo dziękuję za jasne tłumaczenie z dobrze dobranym przykładem!