SQL SERVER (I) Buscar que tabla y campo contiene un valor

 Me parece muy interesantes las repuestas de stackoverflow 

Una de las que más me ha gustado es de sbrbot  que he adaptado un poco para el caso de tipo "string"

DECLARE @search VARCHAR(100), @table SYSNAME, @column SYSNAME, @schema SYSNAME


DECLARE curTabCol CURSOR FOR
    SELECT c.TABLE_SCHEMA , c.TABLE_NAME, c.COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS c
    JOIN INFORMATION_SCHEMA.TABLES t 
      ON t.TABLE_NAME=c.TABLE_NAME AND t.TABLE_TYPE='BASE TABLE' -- avoid views
    WHERE c.DATA_TYPE IN ('varchar','nvarchar','text','ntext','char','nchar') -- searching only in these column types
    --AND c.COLUMN_NAME IN ('NAME','DESCRIPTION') -- searching only in these column names
    ORDER BY c.TABLE_NAME, c.COLUMN_NAME 

SET @search='PHXN'

OPEN curTabCol
FETCH NEXT FROM curTabCol INTO @schema, @table, @column

WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXECUTE('IF EXISTS 
             (SELECT * FROM ' + @schema + '.[' + @table + '] WHERE [' + @column + '] LIKE ''%' + @search + '%'') 
             PRINT ''' + @table + '.' + @column + '''')
    FETCH NEXT FROM curTabCol INTO  @schema, @table, @column
END

CLOSE curTabCol
DEALLOCATE curTabCol

y para tipo "enteros"

DECLARE @search int, @table SYSNAME, @column SYSNAME, @schema SYSNAME

DECLARE curTabCol CURSOR FOR
    SELECT c.TABLE_SCHEMA , c.TABLE_NAME, c.COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS c
    JOIN INFORMATION_SCHEMA.TABLES t 
      ON t.TABLE_NAME=c.TABLE_NAME AND t.TABLE_TYPE='BASE TABLE' -- avoid views
    WHERE c.DATA_TYPE IN ('numeric','int','bigint','decimal') -- searching only in these column types
    --AND c.COLUMN_NAME IN ('NAME','DESCRIPTION') -- searching only in these column names
    ORDER BY c.TABLE_NAME, c.COLUMN_NAME 


--SET @search='SPHXN-YNS5H-9TV6R'
--SET @search='10BC9C2A9F54521E4AE70402CCFC4A87F569F8A8'
SET @search=418534

OPEN curTabCol
FETCH NEXT FROM curTabCol INTO @schema, @table, @column

WHILE (@@FETCH_STATUS = 0)
BEGIN
    --PRINT '                                  ' + @table + '.' + @column
    EXECUTE('IF EXISTS 
             (SELECT * FROM ' + @schema + '.[' + @table + '] WHERE [' + @column + '] = ' + @search + ') 
              PRINT ''' + @table + '.' + @column + '''')
    FETCH NEXT FROM curTabCol INTO @schema, @table, @column
END

CLOSE curTabCol
DEALLOCATE curTabCol