Alle Spalten einer SQL Datenbank nach einem Textinhalt durchsuchen

Posted on by 

ALTER PROCEDURE SP_FindStringInDataBase AS

SET NOCOUNT ON

CREATE TABLE #TableSpace (
 Inhalt int
 )

DECLARE @TableSpace Table (
 T varchar(255),
 C varchar(255),
 Inhalt int
 )

DECLARE
 @Rows int,
 @TableName varchar(255),
 @COLUMNNAME varchar(255),
 @Inhalt int;

DECLARE Table_Cursor CURSOR FOR
 SELECT Top 100 Percent TABLE_NAME, COLUMN_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE (data_type='varchar' or data_type='nvarchar')
 And Column_Name!='Notes2' And Column_Name!='Name' And Column_Name!='Name2' And Column_Name not like 'MailFieldStr%';

OPEN Table_Cursor
 FETCH NEXT FROM Table_Cursor INTO @TableName, @COLUMNNAME;
 WHILE @@FETCH_STATUS = 0
 BEGIN
 DECLARE @sqlCommand nvarchar(1000)
 SET @sqlCommand = 'Select count(*) as Inhalt From ' + @TableName + ' Where ' + @COLUMNNAME + ' Like ''%Suchbegriff%'';'

INSERT INTO #TableSpace ( Inhalt)
 EXEC sp_executesql @sqlCommand

SELECT @Inhalt = Inhalt
 FROM #TableSpace
 DELETE FROM #TableSpace

INSERT INTO @TableSpace (T,C,Inhalt) VALUES (@TableName, @COLUMNNAME, @Inhalt)

FETCH NEXT FROM Table_Cursor INTO @TableName, @COLUMNNAME;
 END

CLOSE Table_Cursor
 DEALLOCATE Table_Cursor
 DROP TABLE #TableSpace

SELECT *
 FROM @TableSpace Where Inhalt>0

Author: Stefan