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
- Seules les colonnes
char,varchar,nchar,nvarchar,intetdecimalsont parcourues. Les autres types, commedateoufloat, sont ignorés. - Chaque ligne de chaque table utilisateur est lue avec une condition
LIKE, donc le script peut durer longtemps sur de grandes bases. Exécutez-le sur une copie de la base ou en dehors des heures de production. - Les correspondances sont stockées dans la table temporaire
#Results, et chaque valeur est tronquée à 3 630 caractères.