if exists (select * from sysobjects where name = 'SearchAllTables')
begin
	DROP PROCEDURE SearchAllTables
end
go

CREATE PROCEDURE SearchAllTables
(
	@SearchVal1	int,
	@SearchVal2	int
	--@SearchStr 	nvarchar(100)
)
AS
BEGIN

	-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
	-- Purpose: To search all columns of all tables for a given search string
	-- Written by: Narayana Vyas Kondreddi
	-- Site: http://vyaskn.tripod.com
	-- Tested on: SQL Server 7.0 and SQL Server 2000
	-- Date modified: 28th July 2002 22:50 GMT

	--CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
	CREATE TABLE #Results (TableName nvarchar(500), ColumnName nvarchar(370), ColumnValue int)

	SET NOCOUNT ON

	DECLARE	@TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
	SELECT	@TableName = ''
	--SELECT	@SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

	WHILE @TableName IS NOT NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName = 
		(
			SELECT 	MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			FROM 	INFORMATION_SCHEMA.TABLES
			WHERE 		TABLE_TYPE = 'BASE TABLE'
				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
		)

		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		BEGIN
			SET @ColumnName =
			(
				SELECT 	MIN(QUOTENAME(COLUMN_NAME))
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 	TABLE_SCHEMA	= PARSENAME(@TableName, 2)
				AND	TABLE_NAME	= PARSENAME(@TableName, 1)
				--AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
				AND	DATA_TYPE IN ('int', 'bigint', 'tinyint', 'numeric', 'decimal')
				AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			)
	
			IF @ColumnName IS NOT NULL
			BEGIN
				INSERT INTO #Results(TableName, ColumnName, ColumnValue)
				EXEC
				(
					'SELECT ''' + @TableName + '''' + ', ''' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
					FROM ' + @TableName + ' (NOLOCK) ' +
					--' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
					' WHERE ' + @ColumnName + ' = ' + @SearchVal1 + 
					' OR ' + @ColumnName + ' = ' + @SearchVal2
				)
			END
		END	
	END

	SELECT 	R.TableName, R.ColumnName, R.ColumnValue 
	FROM 	#Results R
	INNER JOIN
	(
		SELECT 	TableName
		FROM 	#Results
		GROUP BY TableName
		HAVING COUNT(*) > 1
	) AS D
	ON	R.TableName	= D.TableName
END

GO

exec SearchAllTables 140, 30