Find a value anywhere in a database

Given a number, how do I discover in what table and column it could be found within?

I don't care if it's fast, it just needs to work.

This might help you. - from Narayana Vyas. It searches all columns of all tables in a given database. I have used it before and it works.

This is the Stored Proc from the above link - the only change I made was substituting the temp table for a table variable so you don't have to remember to drop it each time.

CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
	SET @ColumnName = ''
	SET @TableName = 
	(
		SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
		FROM 	INFORMATION_SCHEMA.TABLES
		WHERE 		TABLE_TYPE = 'BASE TABLE'
			AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
			AND	OBJECTPROPERTY(
					OBJECT_ID(
						QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
						 ), 'IsMSShipped'
					       ) = 0
	)

	WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
	BEGIN
		SET @ColumnName =
		(
			SELECT MIN(QUOTENAME(COLUMN_NAME))
			FROM 	INFORMATION_SCHEMA.COLUMNS
			WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
				AND	TABLE_NAME	= PARSENAME(@TableName, 1)
				AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
				AND	QUOTENAME(COLUMN_NAME) > @ColumnName
		)

		IF @ColumnName IS NOT NULL
		BEGIN
			INSERT INTO @Results
			EXEC
			(
				'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
				FROM ' + @TableName + 
				' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
			)
		END
	END	
END

SELECT ColumnName, ColumnValue FROM @Results
END

To execute the stored procedure :

 EXEC SearchAllTables 'YourStringHere'

If you need to run such search only once then you can probably go with any of the scripts already shown in other answers. But otherwise, I’d recommend using ApexSQL Search for this. It’s a free SSMS addin and it really saved me a lot of time.

Before running any of the scripts you should customize it based on the data type you want to search. If you know you are searching for datetime column then there is no need to search through nvarchar columns. This will speed up all of the queries above.

Based on bnkdev's answer I modified Narayana's Code to search all columns even numeric ones.

It'll run slower, but this version actually finds all matches not just those found in text columns.

I can't thank this guy enough. Saved me days of searching by hand!

CREATE PROC SearchAllTables 
(
@SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT


CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
	SET @ColumnName = ''
	SET @TableName = 
	(
		SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
		FROM 	INFORMATION_SCHEMA.TABLES
		WHERE 		TABLE_TYPE = 'BASE TABLE'
			AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
			AND	OBJECTPROPERTY(
					OBJECT_ID(
						QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
						 ), 'IsMSShipped'
					       ) = 0
	)

	WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
	BEGIN
		SET @ColumnName =
		(
			SELECT MIN(QUOTENAME(COLUMN_NAME))
			FROM 	INFORMATION_SCHEMA.COLUMNS
			WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
				AND	TABLE_NAME	= PARSENAME(@TableName, 1)					
				AND	QUOTENAME(COLUMN_NAME) > @ColumnName
		)

		IF @ColumnName IS NOT NULL
		BEGIN
			INSERT INTO #Results
			EXEC
			(
				'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(CONVERT(varchar(max), ' + @ColumnName + '), 3630) 
				FROM ' + @TableName + ' (NOLOCK) ' +
				' WHERE CONVERT(varchar(max), ' + @ColumnName + ') LIKE ' + @SearchStr2
			)
		END
	END	
END

SELECT ColumnName, ColumnValue FROM #Results
END

This is my independent take on this question that I use for my own work. It works in SQL2000 and greater, allows wildcards, column filtering, and will search most of the normal data types.

A pseudo-code description could be select * from * where any like 'foo'

--------------------------------------------------------------------------------
-- Search all columns in all tables in a database for a string.
-- Does not search: image, sql_variant or user-defined types.
-- Exact search always for money and smallmoney; no wildcards for matching these.
--------------------------------------------------------------------------------
declare @SearchTerm nvarchar(4000) -- Can be max for SQL2005+
declare @ColumnName sysname

--------------------------------------------------------------------------------
-- SET THESE!
--------------------------------------------------------------------------------
set @SearchTerm = N'foo' -- Term to be searched for, wildcards okay
set @ColumnName = N'' -- Use to restrict the search to certain columns, wildcards okay, null or empty string for all cols
--------------------------------------------------------------------------------
-- END SET
--------------------------------------------------------------------------------

set nocount on

declare @TabCols table (
	  id int not null primary key identity
	, table_schema sysname not null
	, table_name sysname not null
	, column_name sysname not null
	, data_type sysname not null
)
insert into @TabCols (table_schema, table_name, column_name, data_type)
	select t.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE
	from INFORMATION_SCHEMA.TABLES t
		join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_SCHEMA = c.TABLE_SCHEMA
			and t.TABLE_NAME = c.TABLE_NAME
	where 1 = 1
		and t.TABLE_TYPE = 'base table'
		and c.DATA_TYPE not in ('image', 'sql_variant')
		and c.COLUMN_NAME like case when len(@ColumnName) > 0 then @ColumnName else '%' end
	order by c.TABLE_NAME, c.ORDINAL_POSITION

declare
	  @table_schema sysname
	, @table_name sysname
	, @column_name sysname
	, @data_type sysname
	, @exists nvarchar(4000) -- Can be max for SQL2005+
	, @sql nvarchar(4000) -- Can be max for SQL2005+
	, @where nvarchar(4000) -- Can be max for SQL2005+
	, @run nvarchar(4000) -- Can be max for SQL2005+
	
while exists (select null from @TabCols) begin

	select top 1
		  @table_schema = table_schema
		, @table_name = table_name
		, @exists = 'select null from [' + table_schema + '].[' + table_name + '] where 1 = 0'
		, @sql = 'select ''' + '[' + table_schema + '].[' + table_name + ']' + ''' as TABLE_NAME, * from [' + table_schema + '].[' + table_name + '] where 1 = 0'
		, @where = ''
	from @TabCols
	order by id

	while exists (select null from @TabCols where table_schema = @table_schema and table_name = @table_name) begin
		
		select top 1
			  @column_name = column_name
			, @data_type = data_type
		from @TabCols
		where table_schema = @table_schema
			and table_name = @table_name
		order by id
		
		-- Special case for money
		if @data_type in ('money', 'smallmoney') begin
			if isnumeric(@SearchTerm) = 1 begin
				set @where = @where + ' or [' + @column_name + '] = cast(''' + @SearchTerm + ''' as ' + @data_type + ')' -- could also cast the column as varchar for wildcards
			end
		end
		-- Special case for xml
		else if @data_type = 'xml' begin
			set @where = @where + ' or cast([' + @column_name + '] as nvarchar(max)) like ''' + @SearchTerm + ''''
		end
		-- Special case for date
		else if @data_type in ('date', 'datetime', 'datetime2', 'datetimeoffset', 'smalldatetime', 'time') begin
			set @where = @where + ' or convert(nvarchar(50), [' + @column_name + '], 121) like ''' + @SearchTerm + ''''
		end
		-- Search all other types
		else begin
			set @where = @where + ' or [' + @column_name + '] like ''' + @SearchTerm + ''''
		end
		
		delete from @TabCols where table_schema = @table_schema and table_name = @table_name and column_name = @column_name
		
	end
	
	set @run = 'if exists(' + @exists + @where + ') begin ' + @sql + @where + ' print ''' + @table_name + ''' end'
	print @run
	exec sp_executesql @run
	
end

set nocount off

I don't put it in proc form since I don't want to maintain it across hundreds of DBs and it's really for ad-hoc work anyway. Please feel free to comment on bug-fixes.

I optimized Allain Lalonde answer (https://stackoverflow.com/a/436676/412368). Numeric values are still supported. Should be roughly 4-5 times faster (1:03 vs 4:30), tested on a desktop with a 7GB database. http://developer.azurewebsites.net/2015/01/mssql-searchalltables/

IF OBJECT_ID ('dbo.SearchAllTables', 'P') IS NOT NULL 
	DROP PROCEDURE dbo.SearchAllTables;
GO

CREATE PROC SearchAllTables 
(
	@SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Customized and modified: 2014-01-21
-- Tested on: SQL Server 2008 R2

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256)
DECLARE @ColumnName nvarchar(128)
DECLARE @DataType nvarchar(128)

DECLARE @SearchStr2 nvarchar(110)
DECLARE @SearchDecimal decimal(38,19)
DECLARE @Query nvarchar(4000)
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''')
SET @SearchDecimal = CASE WHEN ISNUMERIC(@SearchStr) = 1 THEN CONVERT(decimal(38,19), @SearchStr) ELSE NULL END
PRINT '@SearchStr2: ' + @SearchStr2
PRINT '@SearchDecimal: ' + CAST(@SearchDecimal AS nvarchar)

SET @TableName = ''
WHILE @TableName IS NOT NULL
BEGIN
	SET @ColumnName = ''
	SET @TableName = 
	(
		SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
		FROM    INFORMATION_SCHEMA.TABLES
		WHERE       TABLE_TYPE = 'BASE TABLE'
			AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
			AND OBJECTPROPERTY(
					OBJECT_ID(
						QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
						 ), 'IsMSShipped'
						   ) = 0
	)

	WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
	BEGIN
		SET @ColumnName =
		(
			SELECT MIN(QUOTENAME(COLUMN_NAME))
					DATA_TYPE
			FROM    INFORMATION_SCHEMA.COLUMNS
			WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
				AND TABLE_NAME  = PARSENAME(@TableName, 1)
				AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar',
								  'int', 'bigint', 'tinyint', 'numeric', 'decimal')
				AND QUOTENAME(COLUMN_NAME) > @ColumnName
		)
		SET @DataType =
		(
			SELECT DATA_TYPE
			FROM    INFORMATION_SCHEMA.COLUMNS
			WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
				AND TABLE_NAME  = PARSENAME(@TableName, 1)
				AND QUOTENAME(COLUMN_NAME) = @ColumnName
		)
		PRINT @TableName + '.' + @ColumnName + ' (' + @DataType + ')'

		IF @ColumnName IS NOT NULL
		BEGIN
			IF @DataType IN ('int', 'bigint', 'tinyint', 'numeric', 'decimal')
			BEGIN
				IF @SearchDecimal IS NOT NULL
				BEGIN
					SET @Query = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(CAST(' + @ColumnName + ' AS nvarchar(110)), 3630) ' +
								 'FROM ' + @TableName + ' (NOLOCK) ' +
								 ' WHERE ' + @ColumnName + ' = ' + CAST(@SearchDecimal AS nvarchar)
					PRINT '    ' + @Query
					INSERT INTO @Results
					EXEC (@Query)
				END
			END
			ELSE
			BEGIN
				SET @Query = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) ' +
							 'FROM ' + @TableName + ' (NOLOCK) ' +
							 ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				PRINT '    ' + @Query
				INSERT INTO @Results
				EXEC (@Query)
			END
		END
	END 
END

SELECT ColumnName, ColumnValue FROM @Results
END

I have a solution from a while ago that I kept improving. Also searches within XML columns if told to do so, or searches integer values if providing a integer only string.

/* Reto Egeter, fullparam.wordpress.com */
 
DECLARE	@SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
SET @SearchStrColumnValue = '%searchthis%' /* use LIKE syntax */
SET @FullRowResult = 1
SET @FullRowResultRows = 3
SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */
SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */
SET @SearchStrInXML = 0 /* Searching XML data may be slow */
 
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))
 
SET NOCOUNT ON
 
DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')
DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))
 
WHILE @TableName IS NOT NULL
BEGIN
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND	OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
)
IF @TableName IS NOT NULL
BEGIN
DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(''' + @TableName + ''', 2)
AND TABLE_NAME = PARSENAME(''' + @TableName + ''', 1)
AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')
AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END + ',COLUMN_NAME)'
INSERT INTO @ColumnNameTable
EXEC (@sql)
WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
BEGIN
PRINT @ColumnName
SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),'''
WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),'''
ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + '''
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
INSERT INTO #Results
EXEC(@sql)
IF @@ROWCOUNT > 0 IF @FullRowResult = 1
BEGIN
SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' +
' FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
EXEC(@sql)
END
DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
END	
END
END
SET NOCOUNT OFF
 
SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
GROUP BY TableName, ColumnName, ColumnValue, ColumnType

Source: http://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns/

It's my way to resolve this question. Tested on SQLServer2008R2

CREATE PROC SearchAllTables
@SearchStr nvarchar(100)
AS
BEGIN
DECLARE @dml nvarchar(max) = N''        
IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE dbo.#Results
CREATE TABLE dbo.#Results
 ([tablename] nvarchar(100), 
  [ColumnName] nvarchar(100), 
  [Value] nvarchar(max))  
SELECT @dml += ' SELECT ''' + s.name + '.' + t.name + ''' AS [tablename], ''' + 
                c.name + ''' AS [ColumnName], CAST(' + QUOTENAME(c.name) + 
               ' AS nvarchar(max)) AS [Value] FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) +
               ' (NOLOCK) WHERE CAST(' + QUOTENAME(c.name) + ' AS nvarchar(max)) LIKE ' + '''%' + @SearchStr + '%'''
FROM sys.schemas s JOIN sys.tables t ON s.schema_id = t.schema_id
                   JOIN sys.columns c ON t.object_id = c.object_id
                   JOIN sys.types ty ON c.system_type_id = ty.system_type_id AND c .user_type_id = ty .user_type_id
WHERE t.is_ms_shipped = 0 AND ty.name NOT IN ('timestamp', 'image', 'sql_variant')

INSERT dbo.#Results
EXEC sp_executesql @dml

SELECT *
FROM dbo.#Results
END

I wrote once a tool for myself to do exactly that thing:

[a7 SqlTools][1]

It's free and open-source:

[github link][2]

[1]: http://sqltools.a7.plus "a7 SqlTools" [2]: https://github.com/alekkowalczyk/a7SqlTools

Thanks for the really useful script.

You may need to add the following modification to the code if your tables have non-convertable fields:

SET @ColumnName =
	(
		SELECT MIN(QUOTENAME(COLUMN_NAME))
		FROM 	INFORMATION_SCHEMA.COLUMNS
		WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
			AND	TABLE_NAME	= PARSENAME(@TableName, 1)
			AND	DATA_TYPE NOT IN ('text', 'image', 'ntext')					
			AND	QUOTENAME(COLUMN_NAME) > @ColumnName
	)

Chris

Here, very sweet and small solution:

1) create a store procedure:

create procedure get_table
@find_str varchar(50)
as 
begin
  declare @col_name varchar(500), @tab_name varchar(500);
  declare @find_tab TABLE(table_name varchar(100), column_name varchar(100));
  
  DECLARE tab_col cursor for 
  select C.name as 'col_name', T.name as tab_name
  from sys.tables as T
  left outer join sys.columns as C on  C.object_id=T.object_id
  left outer join sys.types as TP on  C.system_type_id=TP.system_type_id
  where type='U' 
  and TP.name in('text','ntext','varchar','char','nvarchar','nchar');
  
  open tab_col
  fetch next from tab_col into @col_name, @tab_name
  
  while @@FETCH_STATUS = 0
  begin        
    insert into @find_tab 
    exec('select ''' +  @tab_name + ''',''' + @col_name + ''' from ' + @tab_name + 
    ' where ' + @col_name + '=''' + @find_str + ''' group by ' + 
    @col_name + ' having count(*)>0');
   
    fetch next from tab_col into @col_name, @tab_name;
  end
  CLOSE tab_col;  
  DEALLOCATE tab_col; 
  select table_name, column_name from @find_tab;
  
end

==========================

2) call procedure by calling store procedure:
exec get_table 'serach_string';

If you have phpMyAdmin installed use its Search feature.

Select your DataBase.

Be sure you do have selected DataBase, not a table, otherwise you'll get a completely different search dialog.

1. Click Search tab
2. List item Choose the search term you want
3. Choose the tables to search

Another way using JOIN and CURSOR:

USE My_Database;

-- Store results in a local temp table so that.  I'm using a
-- local temp table so that I can access it in SP_EXECUTESQL.
create table #tmp (
	tbl nvarchar(max),
	col nvarchar(max),
	val nvarchar(max)	
);

declare @tbl nvarchar(max);
declare @col nvarchar(max);
declare @q nvarchar(max);
declare @search nvarchar(max) = 'my search key';

-- Create a cursor on all columns in the database
declare c cursor for
SELECT tbls.TABLE_NAME, cols.COLUMN_NAME  FROM INFORMATION_SCHEMA.TABLES AS tbls
JOIN INFORMATION_SCHEMA.COLUMNS AS cols
ON tbls.TABLE_NAME = cols.TABLE_NAME

-- For each table and column pair, see if the search value exists.
open c
fetch next from c into @tbl, @col
while @@FETCH_STATUS = 0
begin
	-- Look for the search key in current table column and if found add it to the results.
	SET @q = 'INSERT INTO #tmp SELECT ''' + @tbl + ''', ''' + @col + ''', ' + @col + ' FROM ' + @tbl + ' WHERE ' + @col + ' LIKE ''%' + @search + '%'''
	EXEC SP_EXECUTESQL @q
	fetch next from c into @tbl, @col
end
close c
deallocate c

-- Get results
select * from #tmp

-- Remove local temp table.
drop table #tmp

-- exec pSearchAllTables 'M54*'

ALTER PROC pSearchAllTables (@SearchStr NVARCHAR(100))
AS
BEGIN
	-- A procedure to search all tables in a database for a value
    -- Note: Use * or % for wildcard

	DECLARE 
		@Results TABLE([Schema.Table.ColumnName] NVARCHAR(370), ColumnValue NVARCHAR(3630))

	SET NOCOUNT ON

	DECLARE 
		@TableName NVARCHAR(256) = ''
		, @ColumnName NVARCHAR(128)		
		, @SearchStr2 NVARCHAR(110) = QUOTENAME(REPLACE(@SearchStr, '*', '%'), '''')
	
	WHILE @TableName IS NOT NULL
		BEGIN
			SET @ColumnName = ''
			SET @TableName = 
			(
				SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
				FROM INFORMATION_SCHEMA.TABLES
				WHERE TABLE_TYPE = 'BASE TABLE'
				AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
			)

			WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
				BEGIN
					SET @ColumnName =
					(
						SELECT MIN(QUOTENAME(COLUMN_NAME))
						FROM INFORMATION_SCHEMA.COLUMNS
						WHERE TABLE_SCHEMA    = PARSENAME(@TableName, 2)
						AND TABLE_NAME  = PARSENAME(@TableName, 1)
						AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
						AND QUOTENAME(COLUMN_NAME) > @ColumnName
					)

					IF @ColumnName IS NOT NULL
						BEGIN
							INSERT INTO @Results 
							EXEC ('SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2)

						END

				END 

		END

	SELECT 
		[Schema.Table.ColumnName]
		, ColumnValue 
	FROM @Results
	GROUP BY 
		[Schema.Table.ColumnName]
		, ColumnValue 

END

You might need to build an inverted index for your database. It is assured to be pretty fast.

For Development purpose you can just export the required tables data into a single HTML and make a direct search on it.

Suppose if you want to get all the table with name a column name contain logintime in the database MyDatabase below is the code sample

    use MyDatabase

    SELECT t.name AS table_name,
    SCHEMA_NAME(schema_id) AS schema_name,
    c.name AS column_name
    FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
    WHERE c.name LIKE '%logintime%'
    ORDER BY schema_name, table_name;

Database client tools (like DBeaver or phpMyAdmin) often support means of fulltext search through entire database.

I was looking for a just a numeric value = 6.84 - using the other answers here I was able to limit my search to this

Declare @sourceTable Table(id INT NOT NULL IDENTITY PRIMARY KEY, table_name varchar(1000), column_name varchar(1000))
Declare @resultsTable Table(id INT NOT NULL IDENTITY PRIMARY KEY, table_name varchar(1000))

Insert into @sourceTable(table_name, column_name)
select schema_name(t.schema_id) + '.' + t.name as[table], c.name as column_name
from sys.columns c
join sys.tables t
on t.object_id = c.object_id
where type_name(user_type_id) in ('decimal', 'numeric', 'smallmoney', 'money', 'float', 'real')
order by[table], c.column_id;

DECLARE db_cursor CURSOR FOR
Select table_name, column_name from @sourceTable
DECLARE @mytablename VARCHAR(1000);
DECLARE @mycolumnname VARCHAR(1000);

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @mytablename, @mycolumnname

WHILE @ @FETCH_STATUS = 0
BEGIN
    Insert into @ResultsTable(table_name)
    EXEC('SELECT ''' + @mytablename + '.' + @mycolumnname + '''  FROM ' + @mytablename + ' (NOLOCK) ' +
    ' WHERE ' + @mycolumnname + '=6.84')
    FETCH NEXT FROM db_cursor INTO @mytablename, @mycolumnname  
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
Select Distinct(table_name) from @ResultsTable

There are lots of workable answers already. I just wanted to share one I wrote that has additional functionality.

--=======================================================================
--	MSSQL Unified Search
--	Minimum compatibility level = 130 (SQL Server 2016)
--		NOTE: The minimum compatibility level is required by the built-in STRING_SPLIT() function.
--			However, you can create the STRING_SPLIT() function at the bottom of this script for
--			lower versions of MSSQL Server.
--
--	Usage:
--		Set the parameters below and execute this script.
--
/************************ Enter Parameters Here ************************/
/**/
/**/	DECLARE @SearchString VARCHAR(1000) = 'insulation installer';  -- Accepts SQL wilcards
/**/
/**/	DECLARE @IncludeUserTables BIT = 1;
/**/	DECLARE @IncludeViews BIT = 0;
/**/	DECLARE @IncludeStoredProcedures BIT = 0;
/**/	DECLARE @IncludeFunctions BIT = 0;
/**/	DECLARE @IncludeTriggers BIT = 0;
/**/
/**/	DECLARE @DebugMode BIT = 0;
/**/	DECLARE @ExcludeColumnTypes NVARCHAR(500) = 'text, ntext, char, nchar, timestamp, bigint, tinyint, smallint, bit, date, time, smalldatetime, datetime, datetime2, real, money, float, decimal, binary, varbinary, image';  -- Comma delimited list
/**/
/***********************************************************************/


SET NOCOUNT ON;
SET @SearchString = QUOTENAME(@SearchString,'''');

DECLARE @Results TABLE ([ObjectType] NVARCHAR(200), [ObjectName] NVARCHAR(200), [ColumnName] NVARCHAR(400), [Value] NVARCHAR(MAX), [SelectStatement] NVARCHAR(1000));
DECLARE @ExcludeColTypes TABLE (system_type_id INT);

INSERT INTO @ExcludeColTypes ([system_type_id])
	SELECT [system_type_id]
	FROM sys.types WHERE
	[name] IN (
		SELECT LTRIM(RTRIM([value])) FROM STRING_SPLIT(@ExcludeColumnTypes,',')
		);

DECLARE @ObjectType NVARCHAR(200);
DECLARE @ObjectName NVARCHAR(200);
DECLARE @Value NVARCHAR(MAX);
DECLARE @SelectStatement NVARCHAR(1000);
DECLARE @Query NVARCHAR(4000);


/********************* Table Objects *********************/
IF (@IncludeUserTables = 1)
BEGIN
	DECLARE @TableObjectId INT = (SELECT MIN([object_id]) FROM sys.tables);
	DECLARE @ColumnId INT;
	WHILE @TableObjectId IS NOT NULL
	BEGIN
	
		SELECT @ObjectType = 'USER TABLE';
		SELECT @ObjectName = '[' + SCHEMA_NAME([schema_id]) + '].[' + OBJECT_NAME(@TableObjectId) + ']' FROM sys.tables WHERE [object_id] = @TableObjectId;

		SET @ColumnId = (SELECT MIN([column_id]) FROM sys.columns WHERE [system_type_id] NOT IN (SELECT [system_type_id] FROM @ExcludeColTypes) AND [object_id] = @TableObjectId);
		WHILE @ColumnId IS NOT NULL
		BEGIN

			SELECT @Value = '[' + [name] +']' FROM sys.columns WHERE [object_id] = @TableObjectId AND column_id = @ColumnId;

			SET @SelectStatement = 'SELECT * FROM ' + @ObjectName + ' WHERE CAST(' + @Value + ' AS NVARCHAR(4000)) LIKE ' + @SearchString + ';';

			SET @Query = 'SELECT '
				+ QUOTENAME(@ObjectType, '''')
				+ ', ' + QUOTENAME(@ObjectName, '''')
				+ ', ' + QUOTENAME(@Value, '''')
				+ ', ' + @Value
				+ ', ''' + REPLACE(@SelectStatement,'''','''''') + ''''
				+ ' FROM ' + @ObjectName
				+ ' WHERE CAST(' + @Value + ' AS NVARCHAR(4000)) LIKE ' + @SearchString + ';';

			IF @DebugMode = 0
			BEGIN
				INSERT INTO @Results EXEC(@Query);
			END;
			ELSE
			BEGIN
				PRINT 'Select Statement:  ' + @SelectStatement;
				PRINT 'Query:  ' + @Query;
			END;

			SET @ColumnId = (SELECT MIN([column_id]) FROM sys.columns WHERE [system_type_id] NOT IN (SELECT [system_type_id] FROM @ExcludeColTypes) AND [object_id] = @TableObjectId AND [column_id] > @ColumnId);
		END;

		SET @TableObjectId = (SELECT MIN([object_id]) FROM sys.tables WHERE [object_id] > @TableObjectId);
	END;
END;

IF @DebugMode = 0
BEGIN
	SELECT 
		[ObjectType]
		,[ObjectName]
		,[ColumnName]
		,[Value]
		,[Count] = CASE
			WHEN [ObjectType] IN ('USER TABLE') THEN COUNT(1)
			ELSE NULL
		END
		,[SelectStatement]
	FROM @Results
	GROUP BY [ObjectType], [ObjectName], [ColumnName], [Value], [SelectStatement]
	ORDER BY [Value];
END;

/********************* Objects Other than Tables *********************/
SET @Query = 'SELECT ' +
	'ObjectType = CASE ' +
		'WHEN b.[type] = ''V'' THEN ''VIEW'' ' +
		'WHEN b.[type] = ''P'' THEN ''STORED PROCEDURE'' ' +
		'WHEN b.[type] = ''FN'' THEN ''SCALAR-VALUED FUNCTION'' ' +
		'WHEN b.[type] = ''IF'' THEN ''TABLE-VALUED FUNCTION'' ' +
		'WHEN b.[type] = ''TR'' THEN ''TRIGGER'' ' +
	'END ' +
	',[ObjectName] = ''['' + SCHEMA_NAME(b.[schema_id]) + ''].['' + OBJECT_NAME(a.[object_id]) + '']'' ' +
	',[ColumnName] = NULL ' +
	',[Value] = a.[definition] ' +
	',[SelectStatement] = ''SP_HELPTEXT '' + QUOTENAME(''['' + SCHEMA_NAME(b.[schema_id]) + ''].['' + OBJECT_NAME(a.[object_id]) + '']'','''''''') + '';'' ' +
'FROM [sys].[sql_modules] a ' +
'JOIN [sys].[objects] b ON a.[object_id] = b.[object_id] ' +
'WHERE ' +
	'( ' +
	'	a.[definition] LIKE ' + @SearchString + 
	') ' +
	'AND ' +
	'( ' +
	'	( ' +
			CAST(@IncludeViews AS VARCHAR(1)) + ' = 1 ' +
	'		AND ' +
	'		b.[type] IN (''V'') ' +
	'	) ' +
	'	OR ' +
	'	( ' +
			CAST(@IncludeStoredProcedures AS VARCHAR(1)) + ' = 1 ' +
	'		AND ' +
	'		b.[type] IN (''P'') ' +
	'	) ' +
	'	OR ' +
	'	( ' +
			CAST(@IncludeFunctions AS VARCHAR(1)) + ' = 1 ' +
	'		AND ' +
	'		b.[type] IN (''FN'',''IF'') ' +
	'	) ' +
	'	OR ' +
	'	( ' +
			CAST(@IncludeTriggers AS VARCHAR(1)) + ' = 1 ' +
	'		AND ' +
	'		b.[type] IN (''TR'') ' +
	'	) ' +
	'); ';

IF @DebugMode = 0
BEGIN
	INSERT INTO @Results EXEC(@Query);
END;
ELSE
BEGIN
	PRINT 'Select Statement:  ' + @SelectStatement;
	PRINT 'Query:  ' + @Query;
END;


/********************** STRING_SPLIT() FUNCTION **********************

CREATE FUNCTION STRING_SPLIT (
    @Expression nvarchar(4000)
    ,@Delimiter nvarchar(100)
)
RETURNS @Ret TABLE ([value] NVARCHAR(4000))
AS
BEGIN

	DECLARE @Start INT = 0, @End INT, @Length INT;
	SELECT @End = CHARINDEX(@Delimiter,@Expression), @Length = @End - @Start;

	IF @End <= 0
	BEGIN
		INSERT INTO @Ret ([value]) VALUES (@Expression);
	END
	ELSE
	BEGIN
		WHILE @Length >= 0
		BEGIN
			INSERT INTO @Ret ([value])
				SELECT ltrim(rtrim(substring(@Expression,@Start,@Length)));
	
			SELECT @Start = @End + LEN(@Delimiter)
			SELECT @End = CHARINDEX(@Delimiter,@Expression,@Start)
			IF @End < 1
				SELECT @End = LEN(@Expression) + 1;
			SELECT @Length = @End - @Start;
	
		END;
	END;
	RETURN;
END;

*********************************************************************/