DECLARE @searchValue varchar(255)
SET @searchValue = 'script'
 
SET NOCOUNT ON
 
DECLARE @TempTable TABLE (table_name varchar(255),column_name varchar(255))
CREATE TABLE #TempTableContent (table_name varchar(255),column_name varchar(255), value text)
 
INSERT INTO @TempTable
SELECT ic.table_schema + '.' + dbo.sysobjects.name AS Tabellenname, dbo.syscolumns.name AS Spaltenname
FROM dbo.syscolumns 
INNER JOIN dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id
INNER JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
INNER JOIN information_schema.COLUMNS ic ON ic.table_name = dbo.sysobjects.name AND ic.column_name = dbo.syscolumns.name
WHERE (dbo.sysobjects.xtype = 'U') AND dbo.systypes.name IN ('varchar', 'nvarchar', 'text', 'char', 'ntext')
 
DECLARE @SQLString varchar(500);
DECLARE @table_name varchar(255), @column_name varchar(255)
 
DECLARE DATA CURSOR LOCAL FAST_FORWARD FOR
SELECT table_name, column_name FROM @TempTable
 
OPEN DATA
 
FETCH NEXT FROM DATA
INTO @table_name, @column_name
 
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SQLString = N'select '''+ @table_name +''' AS table_name, ''' + @column_name +''' AS column_name, [' + @column_name +'] AS value ' + ' FROM ' + @table_name + ' where [' + @column_name + '] LIKE ''%'+@searchValue+'%''';
 
	INSERT INTO #TempTableContent
	EXEC(@SQLString)
 
	FETCH NEXT FROM DATA
	INTO @table_name, @column_name
 
END
SELECT * FROM #TempTableContent
DROP TABLE #TempTableContent