I've been asked this before by people, and I've always said it's possible, but takes too much effort. However, I found a need to search for a value that may be present in any column in any given database. At present, it will search any column with a text-based data type (gleaned from the system_type_id column in sys.columns), and of course could be changed to search for a numeric value (or anything you want for that matter).
Here's the code. It's got some comments in it. You don't need to change anything except the search string and then press F5. It will print out a list of what it's doing, and it will indent any tables that have matching data to make it easier to find them. I may end up changing this to do something else, but this worked for me.
declare @tablename varchar(50) declare @columnname varchar(50) declare @coltablename varchar(50) declare @searchstringvalue varchar(30) declare @columns nvarchar(1000) declare @sql nvarchar(4000) declare @rows int set @searchstringvalue = 'SEARCH FOR ME!!!' -- Put search string here set @columns = '' -- Set a blank value because no one likes NULLs -- This cursor gets the tables in the database that have text fields declare CurTables cursor fast_forward for select top 50 st.[name] from sys.columns sc inner join sys.tables st on sc.object_id = st.object_id where system_type_id in (175, 239, 99, 231, 35, 167, 241) and sc.object_id in (select object_id from sys.tables where [type] = 'U') and max_length > 21 group by st.[name] order by 1 open CurTables fetch next from CurTables into @tablename while @@fetch_status = 0 begin set @columns = CAST((select sc.[name] + ' = ''' + @searchstringvalue + ''' OR ' from sys.columns sc inner join sys.tables st on sc.object_id = st.object_id where st.[name] = @tablename and system_type_id in (175, 239, 99, 231, 35, 167, 241) and sc.object_id in (select object_id from sys.tables where [type] = 'U') and max_length > 21 order by 1 for xml path ('')) AS VARCHAR(MAX)) -- Remove ' OR ' from the end of the string set @columns = SUBSTRING(@columns, 1, LEN(@columns) - 3) set @sql = 'SELECT @numrows = COUNT(1) FROM ' + @tablename + ' WHERE ' + @columns exec sp_executesql @sql, N'@numrows INT OUTPUT', @numrows = @rows OUTPUT; if @rows > 0 begin print CHAR(9) + CHAR(9) + 'Table name: ' + @tablename + ' - ' + CAST(@rows as VARCHAR(10)) + ' row(s) returned.' end else begin print 'Table name: ' + @tablename + ' - ' + CAST(@rows as VARCHAR(10)) + ' row(s) returned.' end set @columns = '' fetch next from CurTables into @tablename end close CurTables deallocate CurTables