DECLARE @SearchValue NVARCHAR(255)
SET @SearchValue = ‘VALUE’— Create a temporary table to store results
CREATE TABLE #Results (
TableName NVARCHAR(128),
ColumnName NVARCHAR(128),
ColumnValue NVARCHAR(4000)
)— Dynamic SQL to search for the pattern in all tables (excluding views)
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = N”SELECT @Sql = @Sql +
‘INSERT INTO #Results (TableName, ColumnName, ColumnValue) ‘ +
‘SELECT ”’ + t.TABLE_NAME + ”’, ”’ + COLUMN_NAME + ”’, ‘ + QUOTENAME(COLUMN_NAME) +
‘ FROM ‘ + QUOTENAME(t.TABLE_SCHEMA) + ‘.’ + QUOTENAME(t.TABLE_NAME) +
‘ WHERE (‘ +
‘ (‘ + QUOTENAME(COLUMN_NAME) + ‘ LIKE @SearchValue COLLATE DATABASE_DEFAULT)’ +
‘ OR (‘ + QUOTENAME(COLUMN_NAME) + ‘ LIKE @SearchValue COLLATE Latin1_General_BIN)’ +
‘);’ + CHAR(13)FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_TYPE = ‘BASE TABLE’ — Exclude views
AND c.DATA_TYPE IN (‘NVARCHAR’, ‘NCHAR’, ‘VARCHAR’, ‘CHAR’, ‘TEXT’)— Execute the dynamic SQL
EXEC sp_executesql @Sql, N’@SearchValue NVARCHAR(255)’, @SearchValue— Retrieve the results
SELECT * FROM #Results— Clean up the temporary table
DROP TABLE #Results
Categories