-- convert row of data to string for reporting
CREATE procedure sp_makerow @tbl_name sysname, @col_name sysname, @IntVariable int, @result nvarchar(max) out
AS
BEGIN
     SET NOCOUNT ON
     DECLARE @SQLString nvarchar(max) = '';
     select @SQLString =  @SQLString + ' + '' | '' + COALESCE(CAST(' + column_name + ' AS nvarchar(64)), ''NULL'')' from information_schema.columns  where table_name = @tbl_name
     SET @SQLString = 'SELECT TOP 1 @rawout = ' + @SQLString + ' FROM ' + @tbl_name + ' where ' + @col_name + ' = @value'
     DECLARE @ParmDefinition nvarchar(64)= N'@value int, @rawout nvarchar(max) OUTPUT';
     EXECUTE sp_executesql @SQLString, @ParmDefinition, @value = @IntVariable, @rawout = @result OUTPUT
END

-- search number in all tables
CREATE procedure sp_searchint @IntVariable int
AS
BEGIN
     SET NOCOUNT ON
     DECLARE @tbl table(table_name sysname, column_name sysname, cnt int, datatext nvarchar(max))
     DECLARE @value sysname, @table_name sysname, @column_name sysname
     DECLARE cFKey CURSOR LOCAL FOR select table_name, column_name from information_schema.columns  where data_type in ('int', 'numeric')
     OPEN cFKey
     FETCH NEXT FROM cFKey INTO @table_name, @column_name
     WHILE @@FETCH_STATUS = 0
         BEGIN
            DECLARE @cnt int = 0;
            DECLARE @ParmDefinition nvarchar(64)= N'@value int, @cntout int OUTPUT';
            DECLARE @SQLString nvarchar(500) = 'select @cntout =count(*) from ' + @table_name + ' where ' + @column_name + ' = @value'
            EXECUTE sp_executesql @SQLString, @ParmDefinition, @value = @IntVariable, @cntout = @cnt OUTPUT
            IF @cnt > 0 
            BEGIN
                declare @result nvarchar(max);
                exec sp_makerow @table_name, @column_name, @IntVariable, @result out        
                INSERT INTO @tbl (table_name, column_name , cnt, datatext) values (@table_name, @column_name, @cnt, @result)
            END
	    FETCH NEXT FROM cFKey INTO @table_name, @column_name
        END
    Close cFKey
    DeAllocate cFKey
    select * FROM @tbl
END
GO


exec sp_searchint 25