Nalezení textového řetězce v databázi

V případě, že máme neznámou nebo rozsáhlou databázi a snažíme se dohledat výskyt určitých dat, aniž bychom tušili, v které tabulce je máme hledat, stojíme před otázkou, jak prohledat všechny tabulky, zda se v nich určitý řetězec vyskytuje. Stejný problém budeme řešit i v případě, že máme databázi, která nepoužívá pro zajištění integrity cizí líče nebo část těchto klíčů chybí, a naším úkolem je z databáze odstranit určitá data. Nejprve je potřebujeme v databázi najít a následně ověřit, že data byla smazána ze všech tabulek, kde se vyskytovala.

Pomocí níže uvedeného skriptu můžeme vyhledat výskyt určitého textového řetězce ve všech tabulkách v databázi, v jejímž kontextu je skript spuštěn. Prohledávat můžeme všechny sloupce, které mají textový datový typ, tedy CHAR, NCHAR, VARCHAR, NVARCHAR, TEXT a NTEXT. V případě unicode datových typů (NCHAR, NVARCHAR, NTEXT) je třeba nezapomenout uvést N před apostrofy s řetězce, který hledáme, jinak nebudou sloupce prohledávány jako unicode a řetězec nebude nalezen, ačkoli v se v databázi vyskytuje.

Skript nejprve naplní cur kursor seznamem všech tabulek v databázi a jejich sloupců, které jsou některého z výše uvedených textových datových typů. Poté je tato vstupní množina iterována a pro každý sloupec je pomocí dynamického sql vytvořen sql dotaz, který hledá pomocí operátoru LIKE náš řetezec ve všech řádcích daného sloupce. Do lokální dočasné tabulky #Result je vložen řádek se jménem tabulky, sloupce a počtem řádků, které obsahují hledaný řetězec. Na konci skriptu jsou z #Result tabulky vybrány ty sloupce, kde se hledaný řetězec vyskytl alespoň jednou.

SET NOCOUNT ON

DECLARE @Stmt VARCHAR(MAX), 
        @SchemaName SYSNAME, 
        @TableName SYSNAME, 
        @ColumnName SYSNAME, 
        @What NVARCHAR(200), 
        @NL CHAR(1),
        @Err NVARCHAR(MAX),
        @Cnt INT

CREATE TABLE #Result (TableName SYSNAME, ColumnName SYSNAME, Cnt INT)

SET @What = N'AKU'

SET @NL = CHAR(13)

DECLARE cur CURSOR FOR 
    SELECT sch.name, t.name, c.name 
    FROM sys.columns c 
        INNER JOIN sys.tables t ON t.object_id = c.object_id
        INNER JOIN sys.types tp ON tp.system_type_id = c.system_type_id
        INNER JOIN sys.schemas sch ON sch.schema_id = t.schema_id
    WHERE tp.name IN ('char','nchar','varchar','nvarchar','text','ntext')
      AND t.type_desc = 'USER_TABLE'
    ORDER BY t.name, c.name

OPEN cur
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM cur INTO @SchemaName, @TableName, @ColumnName
    IF @@FETCH_STATUS <> 0
        BREAK

     PRINT @TableName + ' => ' + @ColumnName
    
    SET @Stmt = 'INSERT INTO #Result' + @NL + 
                '    SELECT ''' +  @TableName + ''', ''' + @ColumnName + ''', COUNT(*)' + @NL +
                '    FROM [' + @SchemaName + '].[' + @TableName + '] (nolock)' + @NL + 
                '    WHERE [' + @ColumnName + '] Like ''%' + @What + '%'''
    BEGIN TRY
        EXEC(@Stmt)
    END TRY
    BEGIN CATCH
    
        SET @Err = '[' + @SchemaName + '].[' + @TableName + '] => ' + ERROR_MESSAGE() + @NL + @Stmt
    
        RAISERROR(@Err, 16, 1) WITH NOWAIT
    
    END CATCH
        
    END

SELECT * FROM #Result WHERE Cnt > 0

Error:
CLOSE cur
DEALLOCATE cur
DROP TABLE #Result