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