Knowledge base
Overený diskutujúci
SprávcaVyhľadávanie dát v celej databáze
V článku si ukážeme, ako je pomocou prostriedkov jazyka Transact-SQL (MS SQL), možné vyhľadávať textovú hodnotu vo všetkých tabuľkách databázy.
Pár dní dozadu, sme s kolegyňou riešili problém, pri ktorom bolo potrebné vyhľadať konkrétnu hodnotu vo všetkých tabuľkách v databáze. Konkrétna databáza obsahovala veľké množstvo tabuliek, ku ktorým bol k dispozícii len slabý popis a manuálne prechádzanie dát, tabuľku po tabuľke, by bolo príliš neefektívne.Dali sme teda hlavy dokopy a pomocou niekoľkých dopytov a troche dynamického kódu sme problém vyriešili. Následne mi ale celý deň vŕtalo v hlave, že prečo si tento proces nezautomatizovať a nemať pripravené riešenie pre podobné situácie. A práve jeho tvorba bude predmetom tohto článku. Krok po kroku si popíšeme jednotlivé časti riešenia a na konci vyskladáme finálne riešenie.
2. Databázu AdventureWorks - Testovaciu databázu od firmy Microsoft (> link <) alebo akúkoľvek inú, ktorej dáta poznáte.
Postupovali sme krok za krokom, aby bol jasný význam každej časti kódu a tým pádom ho bude možné upraviť podľa vašich potrieb. Či už zakomponovaním nových premenných, ktorými by sa menili vyhľadávané typy stĺpcov, operácia vyhľadávania (LIKE namiesto =) a pod. V prípade záujmu, by sme jednotlivé možnosti využitia tohto kódu mohli prebrať v niektorom z nasledujúcich článkov.
An overview of SQL Server data types - sqlshack.com > link <
Diskusia
Čo budeme potrebovať
1. MS SQL Server - Akejkoľvek podporovanej verzie2. Databázu AdventureWorks - Testovaciu databázu od firmy Microsoft (> link <) alebo akúkoľvek inú, ktorej dáta poznáte.
Information schema
Prvým dielikom do pomyselnej skladačky, bude detekcia relevantných polí v tabuľkách, v ktorých môžeme vyhľadávať. Polia musia byť rovnakého alebo kompatibilného typu, ako je vyhľadávaná hodnota. V našom prípade to bude textová hodnota, čiže potrebujeme poznať všetky stĺpce v tabuľkách, ktoré sú typu char, text, varchar, nchar, ntext a nvarchar.Tieto dáta a mnoho ďalších sú dostupné v INFORMATION_SCHEMA. Je to akási sada view-ov, ktorá je dostupná v takmer každej štandardnej relačnej databáze (MS SQL, PostgreSQL, MySQL, ...), obsahujúca informácie o tabuľkách, ich štruktúre ale aj o stored procedúrach a pod. Pozrieme teda, aké údaje nám poskytuje view COLUMNS:Zdrojový kód:
SELECT TOP 100 * FROM [INFORMATION_SCHEMA].[COLUMNS]
Vyžiadali sme si prvých 100 záznamov z view-u COLUMNS. Vo výsledku, okrem názvov konkrétnych stĺpcov (COLUMN_NAME), vidíme aj informáciu o katalógu / databáze (TABLE_CATALOG), schéme (TABLE_SCHEMA), tabuľke (TABLE_NAME), poradí stĺpca v tabuľke (ORDINAL_POSITION) aj typ stĺpca (DATA_TYPE).V našom príklade budeme vyhľadávať textovú hodnotu, preto vyberieme len stĺpce relevantného typu a do výsledku zahrnieme len nevyhnutné údaje. Do WHERE teda pridáme podmienku pre stĺpec DATA_TYPE a namiesto * použijeme zoznam požadovaných údajov:Zdrojový kód:
SELECT [TABLE_SCHEMA] AS 'TableSchema', [TABLE_NAME] AS 'TableName', [COLUMN_NAME] as 'ColumnName'
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [DATA_TYPE] in ('char', 'text', 'varchar', 'text', 'nchar', 'ntext', 'nvarchar')
Aby sme s týmito údajmi mohli ďalej pracovať, vložíme si ich do dočasnej tabuľky, ktorej obsah si vypíšeme a následne ju odstránime cez DROP TABLEFROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [DATA_TYPE] in ('char', 'text', 'varchar', 'text', 'nchar', 'ntext', 'nvarchar')
Zdrojový kód:
SELECT [TABLE_SCHEMA] AS 'TableSchema', [TABLE_NAME] AS 'TableName', [COLUMN_NAME] as 'ColumnName'
INTO #RelevantTables
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [DATA_TYPE] in ('char', 'text', 'varchar', 'text', 'nchar', 'ntext', 'nvarchar')SELECT * FROM #RelevantTablesDROP TABLE #RelevantTables
INTO #RelevantTables
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [DATA_TYPE] in ('char', 'text', 'varchar', 'text', 'nchar', 'ntext', 'nvarchar')SELECT * FROM #RelevantTablesDROP TABLE #RelevantTables
Dynamic SQL
Zoznam stĺpcov, v ktorých chceme vyhľadávať máme, ale chýba nám kód, ktorý by dáta reálne hľadal. Vzhľadom na to, že zoznam stĺpcov bude na každej databáze odlišný, nie je možné aby sme si potrebné query pripravili vopred a musíme ich teda poskladať až za behu, keď je ich zoznam už máme k dispozícii. Na pomoc si teda zoberieme Dynamic SQL, resp. možnosť vykonávať dynamicky generované query.Kód, ktorým sme zobrazovali aktuálny zoznam v dočasnej tabuľke #RelevantTables upravíme tak, aby namiesto zoznamu stĺpcov generoval reťazce, obsahujúce SELECT-y na výber dát z tabuľky, ktorá obsahuje stĺpec v zozname. Hviezdičku nahradíme textovou hodnotou s vhodne pospájaními údajmi zo stĺpcov TableSchema a TableName.Zdrojový kód:
SELECT [TABLE_SCHEMA] AS 'TableSchema', [TABLE_NAME] AS 'TableName', [COLUMN_NAME] as 'ColumnName'
INTO #RelevantTables
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [DATA_TYPE] in ('char', 'text', 'varchar', 'text', 'nchar', 'ntext', 'nvarchar')SELECT 'SELECT * FROM [' + TableSchema + '].[' + TableName + ']' as Query
FROM #RelevantTablesDROP TABLE #RelevantTables
Vo výsledku môžeme vidieť textové hodnoty podobné nasledujúcej: "SELECT * FROM [HumanResources].[vJobCandidate]". V prípade, ak má tabuľka viac textových stĺpcov, bude vo výsledku uvedená viac krát. Zdrojový zoznam obsahuje okrem názvu tabuľky aj konkrétny stĺpec, pričom my skladáme query len na základe názvu tabuľky. Neberieme tak do úvahy konkrétny stĺpec v zozname.Ak sme sa pri zápise nikde nepomýlili, tak ktorýkoľvek údaj z výsledku si môžeme skopírovať do nového okna editora a nechať vykonať. V prípade úspešného vykonania môžeme pokračovať ďalej.V ďalšom kroku rozšírime vygenerovaný kód o podmienku, ktorou budeme hľadať konkrétny záznam v tabuľke, kde už využijeme aj názov stĺpca a pridáme aj premennú @Value, ktorá bude obsahovať hľadaný výraz. Premennú pridáme na začiatok query a priradíme jej požadovanú hodnotu. Skladaný reťazec rozšírime o časť WHERE, v ktorej porovnáme hodnotu stĺpca v zozname s hodnotou v premennej. Aby sme sa vyhli problémom s prípadnou nekompatibilitou typov (text vs varchar) pri porovnávaní, bude hodnota stĺpca pred porovnaním CAST-ovaná na NVARCHAR(MAX) a zároveň zrovnáme COLLATION oboch strán porovnávania na DATABASE_DEFAULT.INTO #RelevantTables
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [DATA_TYPE] in ('char', 'text', 'varchar', 'text', 'nchar', 'ntext', 'nvarchar')SELECT 'SELECT * FROM [' + TableSchema + '].[' + TableName + ']' as Query
FROM #RelevantTablesDROP TABLE #RelevantTables
Zdrojový kód:
DECLARE @Value NVARCHAR(100) = N'value'SELECT [TABLE_SCHEMA] AS 'TableSchema', [TABLE_NAME] AS 'TableName', [COLUMN_NAME] as 'ColumnName'
INTO #RelevantTables
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [DATA_TYPE] in ('char', 'text', 'varchar', 'text', 'nchar', 'ntext', 'nvarchar')SELECT 'SELECT * FROM [' + TableSchema + '].[' + TableName + '] '
+ ' WHERE CAST([' + ColumnName + '] AS NVARCHAR(MAX)) COLLATE DATABASE_DEFAULT = '
+ 'N'''+ REPLACE(@Value, '''', '''''') + ''' COLLATE DATABASE_DEFAULT'
as Query
FROM #RelevantTablesDROP TABLE #RelevantTables
Ako vidíme, zápis je už menej čitateľnejší a preto opätovne odporúčam otestovať funkčnosť poskladanej query z výsledku.Namiesto * v SELECT-e skladanej query, teraz vymenujeme všetky údaje, ktoré budeme vo finálnom výsledku požadovať. Bude to identifikácia tabuľky a stĺpca, kde bol hľadaný výraz nájdený a samozrejme aj nájdená hodnota. INTO #RelevantTables
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [DATA_TYPE] in ('char', 'text', 'varchar', 'text', 'nchar', 'ntext', 'nvarchar')SELECT 'SELECT * FROM [' + TableSchema + '].[' + TableName + '] '
+ ' WHERE CAST([' + ColumnName + '] AS NVARCHAR(MAX)) COLLATE DATABASE_DEFAULT = '
+ 'N'''+ REPLACE(@Value, '''', '''''') + ''' COLLATE DATABASE_DEFAULT'
as Query
FROM #RelevantTablesDROP TABLE #RelevantTables
Zdrojový kód:
DECLARE @Value NVARCHAR(100) = N'value'SELECT [TABLE_SCHEMA] AS 'TableSchema', [TABLE_NAME] AS 'TableName', [COLUMN_NAME] as 'ColumnName'
INTO #RelevantTables
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [DATA_TYPE] in ('char', 'text', 'varchar', 'text', 'nchar', 'ntext', 'nvarchar')SELECT 'SELECT N''' + TableSchema + ''' AS [TableSchema], N''' + TableName + ''' AS [TableName], N''' + ColumnName + ''' AS [ColumnName], CAST([' + ColumnName + '] AS NVARCHAR(MAX)) AS ''Value'' '
+ ' FROM [' + TableSchema + '].[' + TableName + '] '
+ ' WHERE CAST([' + ColumnName + '] AS NVARCHAR(MAX)) COLLATE DATABASE_DEFAULT = '
+ 'N'''+ REPLACE(@Value, '''', '''''') + ''' COLLATE DATABASE_DEFAULT'
as Query
FROM #RelevantTablesDROP TABLE #RelevantTables
Jednotlivé vyhľadávacie query už máme pripravené, už nám ostáva ich len spojiť do finálneho kódu. Pri spájaní využijeme fintu s premennou a funkciou COALESCE, pomocou ktorej vieme elegantne poskladať textové hodnoty aj s nami určeným oddeľovačom, čo v našom prípade bude UNION. Finálny kód priradíme do novej premenne @SQL a následne ho vykonáme cez EXEC.Finálny kód bude teda vyzerať nasledovne:INTO #RelevantTables
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [DATA_TYPE] in ('char', 'text', 'varchar', 'text', 'nchar', 'ntext', 'nvarchar')SELECT 'SELECT N''' + TableSchema + ''' AS [TableSchema], N''' + TableName + ''' AS [TableName], N''' + ColumnName + ''' AS [ColumnName], CAST([' + ColumnName + '] AS NVARCHAR(MAX)) AS ''Value'' '
+ ' FROM [' + TableSchema + '].[' + TableName + '] '
+ ' WHERE CAST([' + ColumnName + '] AS NVARCHAR(MAX)) COLLATE DATABASE_DEFAULT = '
+ 'N'''+ REPLACE(@Value, '''', '''''') + ''' COLLATE DATABASE_DEFAULT'
as Query
FROM #RelevantTablesDROP TABLE #RelevantTables
Zdrojový kód:
DECLARE @Value NVARCHAR(100) = N'value'
DECLARE @SQL NVARCHAR(MAX)SELECT [TABLE_SCHEMA] AS 'TableSchema', [TABLE_NAME] AS 'TableName', [COLUMN_NAME] as 'ColumnName'
INTO #RelevantTables
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [DATA_TYPE] in ('char', 'text', 'varchar', 'text', 'nchar', 'ntext', 'nvarchar')SELECT @SQL = COALESCE(@SQL + ' UNION ', '') + 'SELECT N''' + TableSchema + ''' AS [TableSchema], N''' + TableName + ''' AS [TableName], N''' + ColumnName + ''' AS [ColumnName], CAST([' + ColumnName + '] AS NVARCHAR(MAX)) AS ''Value'' '
+ ' FROM [' + TableSchema + '].[' + TableName + '] '
+ ' WHERE CAST([' + ColumnName + '] AS NVARCHAR(MAX)) COLLATE DATABASE_DEFAULT = '
+ 'N'''+ REPLACE(@Value, '''', '''''') + ''' COLLATE DATABASE_DEFAULT'
FROM #RelevantTablesEXEC(@SQL)DROP TABLE #RelevantTables
DECLARE @SQL NVARCHAR(MAX)SELECT [TABLE_SCHEMA] AS 'TableSchema', [TABLE_NAME] AS 'TableName', [COLUMN_NAME] as 'ColumnName'
INTO #RelevantTables
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [DATA_TYPE] in ('char', 'text', 'varchar', 'text', 'nchar', 'ntext', 'nvarchar')SELECT @SQL = COALESCE(@SQL + ' UNION ', '') + 'SELECT N''' + TableSchema + ''' AS [TableSchema], N''' + TableName + ''' AS [TableName], N''' + ColumnName + ''' AS [ColumnName], CAST([' + ColumnName + '] AS NVARCHAR(MAX)) AS ''Value'' '
+ ' FROM [' + TableSchema + '].[' + TableName + '] '
+ ' WHERE CAST([' + ColumnName + '] AS NVARCHAR(MAX)) COLLATE DATABASE_DEFAULT = '
+ 'N'''+ REPLACE(@Value, '''', '''''') + ''' COLLATE DATABASE_DEFAULT'
FROM #RelevantTablesEXEC(@SQL)DROP TABLE #RelevantTables
Záverom
Vytvorili sme si vyhľadávací kód, ktorý vyhľadáva textový reťazec vo všetkých textových stĺpcoch databázy.Postupovali sme krok za krokom, aby bol jasný význam každej časti kódu a tým pádom ho bude možné upraviť podľa vašich potrieb. Či už zakomponovaním nových premenných, ktorými by sa menili vyhľadávané typy stĺpcov, operácia vyhľadávania (LIKE namiesto =) a pod. V prípade záujmu, by sme jednotlivé možnosti využitia tohto kódu mohli prebrať v niektorom z nasledujúcich článkov.
Použité zdroje
Information schema - Wikipedia > link <An overview of SQL Server data types - sqlshack.com > link <
Žiadne príspevky v diskusii.
Na prispievanie do diskusie musíte byť prihlásený.