SQL търсене навсякъде в БД

Posted on August 16, 2025 by krumch

SQL скрипт за търсене на текстов низ във всички таблици на базата данни, в колоните с текстови типове:

  1. Среда на изпълнение:

0.1. Сървър:
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Oct 19 2012 13:38:57 Copyright (c) Microsoft Corporation Web Edition (64-bit) on Windows NT 6.2 (Build 9200: )

0.2. Клиент:
SQL Server Management Studio 20.2.37.0
SQL Server Management Objects (SMO) 17.100.40.0+f57178c95d4376485d3f597f2b829bbd6f80fd6f
Microsoft T-SQL Parser 17.2.3.1+46115224c373754fdd41516eaae1386cabc4819e.46115224c373754fdd41516eaae1386cabc4819e
Microsoft Analysis Services Client Tools 20.0.3.0
Microsoft Data SqlClient (MDS) 5.1.5
Microsoft SQL Server Data-Tier Application Framework (DacFX) 162.3.566.1+89d89fe935702c8836ebaf6a03cf61b85118f847.89d89fe935702c8836ebaf6a03cf61b85118f847
Microsoft .NET Framework 4.0.30319.42000
Operating System 10.0.22631

  1. „Dry run“ - предварителен преглед на местата, в които ще бъдат извършени промени:
SET NOCOUNT ON;
DECLARE
@SearchTerm nvarchar(4000) = N'I''ll', -- какво търсим
@ReplaceTerm nvarchar(4000) = N'I will', -- с какво заменяме (само визуализация тук)
@SchemaFilter sysname = NULL, -- напр. N'dbo'; NULL = всички схеми
@CaseSensitive bit = 0; -- 0=case-insensitive, 1=case-sensitive
-- Затваряне/освобождаване на евентуален стар курсор
IF (CURSOR_STATUS('local','col_cursor') >= -1) OR
(CURSOR_STATUS('global','col_cursor') >= -1)
BEGIN
BEGIN TRY CLOSE col_cursor; END TRY BEGIN CATCH END CATCH;
BEGIN TRY DEALLOCATE col_cursor; END TRY BEGIN CATCH END CATCH;
END
-- Чистим темп таблици
IF OBJECT_ID('tempdb..#Preview') IS NOT NULL DROP TABLE #Preview;
IF OBJECT_ID('tempdb..#TextColumns') IS NOT NULL DROP TABLE #TextColumns;
-- Резултат без MatchCount колона (ще го изчислим накрая с WINDOW функция)
CREATE TABLE #Preview
(
SchemaName sysname,
TableName sysname,
ColumnName sysname,
KeyText nvarchar(max) NULL, -- PK като "Col=Val|Col2=Val2" или IDENTITY=…
BeforeSlice nvarchar(4000) NULL, -- откъс преди
AfterSlice nvarchar(4000) NULL -- откъс след (визуализация)
);
-- Всички текстови колони
CREATE TABLE #TextColumns
(
SchemaName sysname,
TableName sysname,
ColumnName sysname,
object_id int,
column_id int,
TypeName sysname
);
INSERT INTO #TextColumns (SchemaName, TableName, ColumnName, object_id, column_id, TypeName)
SELECT
s.name, t.name, c.name, t.object_id, c.column_id, ty.name
FROM sys.schemas s
JOIN sys.tables t ON t.schema_id = s.schema_id AND t.is_ms_shipped = 0
JOIN sys.columns c ON c.object_id = t.object_id AND c.is_computed = 0
JOIN sys.types ty ON ty.user_type_id = c.user_type_id
WHERE (@SchemaFilter IS NULL OR s.name = @SchemaFilter)
AND ty.name IN (N'varchar', N'nvarchar', N'char', N'nchar', N'text', N'ntext');
-- Колация за case-sensitive при нужда
DECLARE @CollationSuffix nvarchar(200) = CASE WHEN @CaseSensitive = 1
THEN N' COLLATE SQL_Latin1_General_CP1_CS_AS' ELSE N'' END;
-- Курсор по всички текстови колони
DECLARE col_cursor CURSOR FAST_FORWARD FOR
SELECT SchemaName, TableName, ColumnName, object_id
FROM #TextColumns
ORDER BY SchemaName, TableName, ColumnName;
DECLARE
@SchemaName sysname,
@TableName sysname,
@ColumnName sysname,
@ObjectId int;
OPEN col_cursor;
FETCH NEXT FROM col_cursor INTO @SchemaName, @TableName, @ColumnName, @ObjectId;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE
@fqtn nvarchar(512) = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName),
@col nvarchar(512) = 't.' + QUOTENAME(@ColumnName);
-- PK → конкатениран текст; ако няма PK → IDENTITY; иначе NULL
DECLARE @KeyConcat nvarchar(max) = N'';
SELECT @KeyConcat = STUFF((
SELECT
' + ''|'' + ''' + c.name + '='' + CONVERT(nvarchar(4000), t.' + QUOTENAME(c.name) + ')'
FROM sys.indexes i
JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE i.object_id = @ObjectId AND i.is_primary_key = 1
ORDER BY ic.key_ordinal
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 8, '');
DECLARE @IdentityCol sysname =
(
SELECT TOP (1) c.name
FROM sys.columns c
WHERE c.object_id = @ObjectId AND c.is_identity = 1
ORDER BY c.column_id
);
DECLARE @KeyExpr nvarchar(max);
IF (@KeyConcat IS NOT NULL AND LEN(@KeyConcat) > 0)
SET @KeyExpr = '(' + @KeyConcat + ')';
ELSE IF @IdentityCol IS NOT NULL
SET @KeyExpr = '( ''IDENTITY='' + CONVERT(nvarchar(4000), t.' + QUOTENAME(@IdentityCol) + '))';
ELSE
SET @KeyExpr = 'NULL';
-- Визуализация за тази колона (без UPDATE) - всичко през NVARCHAR(MAX) заради text/ntext
DECLARE @sql nvarchar(max) = N'
;WITH src AS
(
SELECT
' + @KeyExpr + N' AS KeyText,
TRY_CONVERT(nvarchar(max),' + @col + N')' + @CollationSuffix + N' AS Val
FROM ' + @fqtn + N' AS t
WHERE TRY_CONVERT(nvarchar(max),' + @col + N')' + @CollationSuffix + N' LIKE @pattern ESCAPE ''~''
)
INSERT INTO #Preview (SchemaName, TableName, ColumnName, KeyText, BeforeSlice, AfterSlice)
SELECT
N' + QUOTENAME(@SchemaName,'''') + N',
N' + QUOTENAME(@TableName ,'''') + N',
N' + QUOTENAME(@ColumnName,'''') + N',
KeyText,
SUBSTRING(REPLACE(REPLACE(Val, CHAR(13), N'' ''), CHAR(10), N'' ''),
CASE WHEN CHARINDEX(@needle, Val) > 60 THEN CHARINDEX(@needle, Val) - 60 ELSE 1 END, 180),
SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(Val, @needle, @replace), CHAR(13), N'' ''), CHAR(10), N'' ''), CHAR(9), N'' ''),
CASE WHEN CHARINDEX(@replace, REPLACE(Val, @needle, @replace)) > 60
THEN CHARINDEX(@replace, REPLACE(Val, @needle, @replace)) - 60 ELSE 1 END, 180)
FROM src;';
DECLARE
@needle nvarchar(4000) = @SearchTerm,
@replace nvarchar(4000) = @ReplaceTerm,
@pattern nvarchar(4000);
SET @pattern = N'%' +
REPLACE(REPLACE(REPLACE(REPLACE(@SearchTerm, N'~', N'~~'), N'%', N'~%'), N'', N'~'), N'[', N'~[')
+ N'%';
EXEC sp_executesql
@sql,
N'@needle nvarchar(4000), @replace nvarchar(4000), @pattern nvarchar(4000)',
@needle = @needle,
@replace = @replace,
@pattern = @pattern;
FETCH NEXT FROM col_cursor INTO @SchemaName, @TableName, @ColumnName, @ObjectId;
END
CLOSE col_cursor;
DEALLOCATE col_cursor;
-- Финален преглед: броят на съвпаденията се изчислява на момента с COUNT() OVER(…) SELECT SchemaName, TableName, ColumnName, KeyText, BeforeSlice, AfterSlice, COUNT() OVER (PARTITION BY SchemaName, TableName, ColumnName) AS MatchCount
FROM #Preview
ORDER BY SchemaName, TableName, ColumnName;
  1. Замяна на текстов низ
SET NOCOUNT ON;
DECLARE
@SearchTerm nvarchar(4000) = N'I''ll', -- какво търсим
@ReplaceTerm nvarchar(4000) = N'I will', -- с какво заменяме
@SchemaFilter sysname = NULL, -- напр. N'dbo'; NULL = всички схеми
@CaseSensitive bit = 0; -- 0=case-insensitive, 1=case-sensitive
IF (CURSOR_STATUS('local','col_cursor') >= -1) OR
(CURSOR_STATUS('global','col_cursor') >= -1)
BEGIN
BEGIN TRY CLOSE col_cursor; END TRY BEGIN CATCH END CATCH;
BEGIN TRY DEALLOCATE col_cursor; END TRY BEGIN CATCH END CATCH;
END
IF OBJECT_ID('tempdb..#TextColumns') IS NOT NULL DROP TABLE #TextColumns;
CREATE TABLE #TextColumns
(
SchemaName sysname,
TableName sysname,
ColumnName sysname,
object_id int,
column_id int,
TypeName sysname
);
INSERT INTO #TextColumns (SchemaName, TableName, ColumnName, object_id, column_id, TypeName)
SELECT
s.name, t.name, c.name, t.object_id, c.column_id, ty.name
FROM sys.schemas s
JOIN sys.tables t ON t.schema_id = s.schema_id AND t.is_ms_shipped = 0
JOIN sys.columns c ON c.object_id = t.object_id AND c.is_computed = 0
JOIN sys.types ty ON ty.user_type_id = c.user_type_id
WHERE (@SchemaFilter IS NULL OR s.name = @SchemaFilter)
AND ty.name IN (N'varchar', N'nvarchar', N'char', N'nchar', N'text', N'ntext');
DECLARE @CollationSuffix nvarchar(200) = CASE WHEN @CaseSensitive = 1
THEN N' COLLATE SQL_Latin1_General_CP1_CS_AS' ELSE N'' END;
DECLARE col_cursor CURSOR FAST_FORWARD FOR
SELECT SchemaName, TableName, ColumnName, object_id
FROM #TextColumns
ORDER BY SchemaName, TableName, ColumnName;
DECLARE
@SchemaName sysname,
@TableName sysname,
@ColumnName sysname,
@ObjectId int;
OPEN col_cursor;
FETCH NEXT FROM col_cursor INTO @SchemaName, @TableName, @ColumnName, @ObjectId;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @fqtn nvarchar(512) = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName);
DECLARE @col nvarchar(512) = QUOTENAME(@ColumnName);
DECLARE @sql nvarchar(max) = N'
UPDATE ' + @fqtn + '
SET ' + @col + ' = REPLACE(CONVERT(nvarchar(max),' + @col + ')' + @CollationSuffix + N', @needle, @replace)
WHERE CONVERT(nvarchar(max),' + @col + ')' + @CollationSuffix + N' LIKE @pattern ESCAPE ''~'';';
DECLARE
@needle nvarchar(4000) = @SearchTerm,
@replace nvarchar(4000) = @ReplaceTerm,
@pattern nvarchar(4000);
SET @pattern = N'%' +
REPLACE(REPLACE(REPLACE(REPLACE(@SearchTerm, N'~', N'~~'), N'%', N'~%'), N'', N'~'), N'[', N'~[')
+ N'%';
PRINT '--- Updating ' + @fqtn + '.' + @col;
EXEC sp_executesql
@sql,
N'@needle nvarchar(4000), @replace nvarchar(4000), @pattern nvarchar(4000)',
@needle = @needle,
@replace = @replace,
@pattern = @pattern;
FETCH NEXT FROM col_cursor INTO @SchemaName, @TableName, @ColumnName, @ObjectId;
END
CLOSE col_cursor;
DEALLOCATE col_cursor;

Публикувам скрипта, защото отделих няколко часа, за да го прецизирам, и се надявам да бъде полезен и на други.

Share this

This entry was posted in Blog, Hacks. Bookmark the permalink.

Leave a Reply