Internal

/*
Provided as is – USE IT AT YOU OWN RISK!!!
*/
DECLARE @SearchForValue AS VARCHAR(255)
DECLARE @SearchTable AS VARCHAR(255)
DECLARE @SearchColumn AS VARCHAR(255)
DECLARE @SchemaName AS VARCHAR(255)
DECLARE @MaxRow AS INT
DECLARE @CurrentRow AS INT
DECLARE @ValueTypeName AS VARCHAR(255)

SET @SearchForValue = ‘ENTER-SEARCHVALUE-HERE’

/* *************************************************************************** */
/* Start: check if Temp Table exists and if so drop it and create temp table */
/* *************************************************************************** */
IF EXISTS(SELECT Name FROM tempdb.sys.tables
WHERE Name LIKE ‘%#CheckTableValues%’)
BEGIN
DROP TABLE #CheckTableValues
END

CREATE TABLE #CheckTableValues
(
ID INT IDENTITY(1,1),
TabName VARCHAR(255),
ColName VARCHAR(255),
SchemaName VARCHAR(255),
AnzWerte INT,
ValueTypeName VARCHAR(255)
)
/* *************************************************************************** */
/* End: Check if Temp Table exists and if so drop it and create temp table */
/* *************************************************************************** */

/* *************************************************************************** */
/* Start: Fill Temp Table with the columns in question */
/* *************************************************************************** */
INSERT INTO #CheckTableValues (TabName, ColName, SchemaName, AnzWerte, ValueTypeName)
SELECT
T2.TabName, T1.Name, T2.SchemaName, -1, T3.TypeName
FROM
sys.columns AS T1
INNER JOIN
(
SELECT ST.name AS TabName, ST.object_id AS OID, SySch.Name AS SchemaName FROM sys.tables AS ST
INNER JOIN sys.schemas AS SySch ON ST.schema_id = SySch.schema_id
WHERE ST.type_desc = ‘USER_TABLE’
)AS T2
ON
T1.object_id = T2.OID
INNER JOIN
(
SELECT name AS TypeName, system_type_id AS sTypeID, user_type_id AS uTypeID FROM sys.types
)AS T3
ON T1.system_type_id = T3.sTypeID
AND T1.user_type_id = T3.uTypeID
/*
Possible Types are: ‘ntext’, ‘tinyint’, ‘bigint’, ‘uniqueidentifier’, ‘char’, ‘binary’, ‘nvarchar’, ‘varchar’, ‘int’ or parts of these strings
The following example is for text like columns …
*/
WHERE
T3.TypeName LIKE ‘%text%’
OR
T3.TypeName LIKE ‘%char%’
/* *************************************************************************** */
/* End: Fill Temp Table with the columns in question */
/* *************************************************************************** */

/* *************************************************************************** */
/* Start: Loop through all columns in question and store results */
/* *************************************************************************** */
SET @CurrentRow = 1
SELECT @MaxRow = MAX(ID) FROM #CheckTableValues

DECLARE @SQL AS VARCHAR(8000)

WHILE @CurrentRow <= @MaxRow
BEGIN
SELECT @SearchTable = TabName FROM #CheckTableValues WHERE ID = @CurrentRow
SELECT @SearchColumn = ColName FROM #CheckTableValues WHERE ID = @CurrentRow
SELECT @SchemaName = SchemaName FROM #CheckTableValues WHERE ID = @CurrentRow
SELECT @ValueTypeName = ValueTypeName FROM #CheckTableValues WHERE ID = @CurrentRow

IF @ValueTypeName like ‘%text%’ OR @ValueTypeName like ‘%char%’
BEGIN
SET @SQL = ‘
DECLARE @AnzWerte AS INT
SELECT @AnzWerte = COUNT(*) FROM [‘ + @SchemaName + ‘].[‘ + @SearchTable + ‘] WHERE [‘ + @SearchColumn + ‘] LIKE ”%’ + @SearchForValue + ‘%”

UPDATE #CheckTableValues
SET AnzWerte = @AnzWerte
WHERE ID = ‘ + CAST(@CurrentRow AS VARCHAR)
END
ELSE
BEGIN
SET @SQL = ‘
DECLARE @AnzWerte AS INT
SELECT @AnzWerte = COUNT(*) FROM [‘ + @SchemaName + ‘].[‘ + @SearchTable + ‘] WHERE [‘ + @SearchColumn + ‘] = ”’ + @SearchForValue + ”’

UPDATE #CheckTableValues
SET AnzWerte = @AnzWerte
WHERE ID = ‘ + CAST(@CurrentRow AS VARCHAR)
END

EXEC (@SQL)
SET @CurrentRow = @CurrentRow + 1
END

/* *************************************************************************** */
/* End: Loop through all columns in question and store results */
/* *************************************************************************** */

/* *************************************************************************** */
/* Show results (temp tables will be deleted automatically with disconnect) */
/* *************************************************************************** */

SELECT TabName, ColName, AnzWerte, @SearchForValue as ValueToSearch
FROM #CheckTableValues ORDER BY AnzWerte DESC, TabName, ColName