LLI Wiki — My Digital Knowledge Base

Rechercher une chaîne dans toutes les colonnes des tables SQL Server

Script T-SQL qui cherche une chaîne dans les colonnes de type texte et numérique de toutes les tables utilisateur d'une base.

Le script ci-dessous recherche une chaîne dans chaque colonne de chaque table utilisateur de la base courante. Il renvoie, pour chaque correspondance, le nom de la table et de la colonne ainsi que la valeur, tronquée aux 3 630 premiers caractères.

Script

Indiquez le texte à chercher dans @SearchStr. Les caractères % sont ajoutés par le script.

DECLARE @SearchStr NVARCHAR(100), @SearchStr2 NVARCHAR(110), @TableName NVARCHAR(256), @ColumnName NVARCHAR(128)
-------------------------------------------------------------------------------------------------------------
--Set searched string here
SET @SearchStr = 'rolback'
-------------------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#Results','U') IS NOT NULL
DROP TABLE #Results
CREATE TABLE #Results (ColumnName NVARCHAR(370), ColumnValue NVARCHAR(3630))
SET NOCOUNT ON
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name))
FROM sys.tables
WHERE type_desc = 'USER_TABLE'
AND QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(c.name))
FROM sys.columns c
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE OBJECT_SCHEMA_NAME(object_id) = PARSENAME(@TableName, 2)
AND OBJECT_NAME(object_id) = PARSENAME(@TableName, 1)
AND t.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
AND QUOTENAME(c.name) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results

Remarques

Mastodon LLIMastodon SocialFosstodon