SQL-Server : Find value of a column in entire database

Some times we need to find one column is present in how many tables with the same value. For example I have employee database. In this empid column is present in 7 table. Now I want to check empid 101 is present in how many table. This we can find with below stored procedure.

CREATE PROCEDURE GetColumnValueInDatabase 
        @value VARCHAR(max) --value which we want to search

       ,@searchColumn VARCHAR(250)--specify the column name for which we need to search the value

AS

BEGIN

       DECLARE @qry VARCHAR(max)

       DECLARE @tabl TABLE (

              table_name VARCHAR(350)

              ,columnname VARCHAR(350)

              ,isprocessed BIT

              ,tableschema VARCHAR(5)

              )

       DECLARE @tabl_1 TABLE (

              table_name VARCHAR(350)

              ,columnname VARCHAR(350)

              )

       INSERT INTO @tabl

       SELECT tbls.TABLE_NAME

              ,cols.COLUMN_NAME

              ,0

              ,tbls.TABLE_SCHEMA

       FROM INFORMATION_SCHEMA.TABLES AS tbls

       JOIN INFORMATION_SCHEMA.COLUMNS AS cols ON tbls.TABLE_NAME = cols.TABLE_NAME

       WHERE cols.COLUMN_NAME = @searchColumn

       DECLARE @table_name VARCHAR(350)

              ,@columnname VARCHAR(350)

              ,@tblSchema VARCHAR(5)

       WHILE EXISTS (

                     SELECT 1

                     FROM @tabl

                     WHERE isprocessed = 0

                     )

       BEGIN

              SELECT TOP 1 @table_name = table_name

                     ,@columnname = columnname

                     ,@tblSchema = tableschema

              FROM @tabl

              WHERE isprocessed = 0

              ORDER BY table_name DESC

              SET @qry = 'SELECT ''' + @table_name + ''', ''' + @columnname + ''' FROM ' + @tblSchema + '.' + @table_name + ' where ' + @columnname + ' = ' + @value

              PRINT (@qry)

              INSERT @tabl_1

              EXEC (@qry)

              UPDATE @tabl

              SET isprocessed = 1

              WHERE table_name = @table_name

                     AND columnname = @columnname

       END

       SELECT *

       FROM @tabl_1

END

Example

EXEC GetColumnValueInDatabase 101,'empid'

Thx,
RS