USE [<DatenbankName>] -- Hier auf die gewünschte Datenbank umstellen
GO
SET NOCOUNT ON;
DECLARE @RowNumber INT;
DECLARE @SearchFor NVARCHAR(100);
SET @SearchFor = '%Berlin%';
DECLARE @Sql NVARCHAR(MAX);
DECLARE @SqlCount NVARCHAR(MAX);
DECLARE @TotalCount INT;
DECLARE @TableName NVARCHAR(200);
DECLARE @ColumnName NVARCHAR(200);
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
SELECT T.name as TableName,
C.name as ColumnName,
ROW_NUMBER() OVER (ORDER BY T.name, C.name) as RowNumber
INTO #tmp
FROM sys.tables T
INNER JOIN sys.columns C
ON C.object_id = T.object_id
INNER JOIN sys.types TY
ON C.system_type_id = TY.system_type_id
WHERE T.is_ms_shipped = 0 -- Nur die eigenen Tabellen berücksichtigen (also nicht System-Tabellen)
AND TY.name NOT IN ('int', 'smallint', 'uniqueidentifier', 'tinyint', 'datetime', 'decimal', 'sysname', 'bit', 'bigint', 'float', 'varbinary', 'float', 'xml', 'smalldatetime', 'char', 'numeric')
SET @RowNumber = @@ROWCOUNT;
WHILE (@RowNumber > 0)
BEGIN
SELECT @TableName = TableName, @ColumnName = ColumnName FROM #tmp WHERE RowNumber = @RowNumber;
SET @Sql = 'SELECT ''' + @TableName + ''' AS TableName, ''' + @ColumnName + ''' AS ColumnName, * FROM ' + @TableName + ' WHERE ' + @ColumnName + ' LIKE ''' + @SearchFor + '''';
SET @SqlCount = 'SELECT @TotalCount = COUNT(*) FROM (' + @Sql + ') A';
EXEC sp_executesql @SqlCount, N'@TotalCount int OUTPUT', @TotalCount OUTPUT
IF (@TotalCount > 0)
BEGIN
PRINT '--------------------------------------------------------------------------------------------'
PRINT @TotalCount;
PRINT @SQL;
EXEC(@Sql);
END;
SET @RowNumber = @RowNumber - 1;
END;
GO