Create Procedure [dbo].[sp_ColumnValues] 
@iTableName		varchar(100),
@iColumnName	       varchar(100),
@iWhere			varchar(100),
@idataType		varchar(100),
@ivalue			varchar(100)

as
/*

Requires following inputs:

@iTableName  - Table to be searched. Use wildcard '%' to search all tables.
@iColumnName - Column to be searched. Use wildcard '%' to search all Columns.  
				Unlikely to work when searching multiple tables.
@iWhere		 - Criteria to restrict results.  Use the operator "in" to select multiple results.  
                Use an expression that is always true such as 1 = 1 to get all results.  
                This may result in a very long running query. 
                Unlikely to work when searching multiple tables.
@idataType	 - use this value to only search certain datatype ("int","char","decimal","smalldatetime")
				Use wildcard '%' to search all datatypes.
@ivalue		 - value to be search for.  Can use wildcards to broaden search. 

Example

	EXECUTE [MISDB].[dbo].[sp_ColumnValues] 
		@iTableName = 'accounts'
		,@iColumnName = '%'
		,@iWhere = 'acct_nbr = 99999999'
		,@idataType = 'decimal'
		,@ivalue = '''%.62'''

*/

If Len(@iTableName) = 0 set @iTableName = '%'
If Len(@iColumnName) = 0 set @iColumnName = '%'

IF OBJECT_ID (N'tempdb.dbo.#Tables',N'U' ) IS NOT NULL 
	Begin
		Drop Table #Tables
	End

Create Table #Tables(
TABLE_Qualifier varchar(100),
Table_Owner		varchar(100),
Table_Name		varchar(100),
Table_Type		varchar(100),
Remarks			varchar(100))


insert #Tables Exec sp_Tables 

IF OBJECT_ID (N'tempdb.dbo.##Columns',N'U' ) IS NOT NULL 
	Begin
		Drop Table ##Columns
	End

Create Table ##Columns(
TABLE_QUALIFIER	sysname,
TABLE_OWNER	sysname,
TABLE_NAME	sysname,
COLUMN_NAME	sysname,
DATA_TYPE	smallint,
TYPE_NAME	sysname,
iPRECISION	int,
LENGTH	int,
SCALE	smallint,
RADIX	smallint,
NULLABLE	smallint,
REMARKS	varchar(254),
COLUMN_DEF	nvarchar(4000),
SQL_DATA_TYPE	smallint,
SQL_DATETIME_SUB	smallint,
CHAR_OCTET_LENGTH	int,
ORDINAL_POSITION	int,
IS_NULLABLE	varchar(254),
SS_DATA_TYPE	tinyint)



DECLARE @getTableID CURSOR
DECLARE @TableName varchar(100)

	SET @getTableID = CURSOR FOR
	SELECT Table_Name
	FROM #Tables
	Where Table_Type = 'TABLE' and Table_Name like @iTableName
	OPEN @getTableID

	FETCH NEXT FROM @getTableID INTO @TableName
	WHILE @@FETCH_STATUS = 0
	BEGIN

		insert ##Columns Exec sp_Columns @table_name = @TableName

	FETCH NEXT FROM @getTableID INTO @TableName
	END

	CLOSE @getTableID
	DEALLOCATE @getTableID

DECLARE @SqlStatement nvarchar(4000)

IF OBJECT_ID (N'tempdb.dbo.##ColumnValues',N'U' ) IS NOT NULL 
	Begin
		Drop Table ##ColumnValues
	End

Create Table ##ColumnValues(
cValue		varchar(100),
ColumnName	varchar(100))

DECLARE @getValueID CURSOR
DECLARE @vcolumn varchar(100)
Declare @vTable		varchar(100)

	SET @getValueID = CURSOR FOR
	SELECT Column_Name,Table_Name
	from ##Columns 
	Where Column_Name like @iColumnName and Type_Name like @iDataType

	OPEN @getValueID

	FETCH NEXT FROM @getValueID INTO @vColumn,@vTable

	WHILE @@FETCH_STATUS = 0
	BEGIN
	
	Select @SqlStatement = 
	'Insert Into ##ColumnValues 
	Select Convert(varchar(100),' + @vColumn + ') , ''' + @vColumn + ''' From ' + @vTable + ' Where ' + @iWhere 

	--Select @SqlStatement
	EXEC sp_executesql @SqlStatement

	FETCH NEXT FROM @getValueID INTO @vColumn,@vTable
	END

	CLOSE @getValueID
	DEALLOCATE @getValueID

-- Return columns that match criteria

Select @SqlStatement = '
Select *
From ##ColumnValues 
Where cvalue like ' + @ivalue

EXEC sp_executesql @SqlStatement