MS SQL-Server

Knowledge Base

MS SQL-Server > Lösungen, Rezepte, Tipps, Fragen

Suche nach Wort in allen Tabellen

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