SQL Server > Find a specific value in all the tables of a database using T-SQL

, September 2, 2022 3028 Views

Often times we find ourselves in a situation where we want to find a particular value but we are unaware of its whereabouts, i.e., we don’t know in which table or column this value is present. Or we want to know in which other tables a particular value is present. Or just if this value exists or not in the database.

This will just be like finding a needle in a haystack if we manually start finding it in every table and column.

Let us show you what we are talking about.

I want to know in which columns and tables, the value “Tom Cruise” is stored in the database.

We have built a query that can loop inside each table & each column and search for the required value and we can get an output like this:

This will reduce manual work and will be more effective & time efficient.

To get the above result, we just have to pass the value to be searched and the data type of the value. We can even pass the schema if we need.

Below is the SQL query which can be used for this magical output:

--Let’s create temp tables that will store the value of tablenames and columnnames in which the specified value is found.
--temp table to store table name and column name from database
CREATE TABLE #tempTableColumn 
    Table_Name VARCHAR(100),
    Column_Name VARCHAR(100)

--temp table for storing final output
CREATE TABLE #tempTableFinal 
    Table_Name VARCHAR(100),
    Column_Name VARCHAR(100),
    SearchedValue NVARCHAR(max) --datatype of the value

--Set the value that needs to be searched in the database
DECLARE @SearchValue NVARCHAR(max) = 'Tom Cruise'

--Set the datatype of the value that needs to be searched in the database

--Set the schema of the tables that needs to be searched in the database
DECLARE @Schema VARCHAR(50) = 'dbo'

--To store all table names and columns having datatype as of searchvalue to be used for looping
INSERT INTO #tempTableColumn 
    Data_Type IN (@DataType)
AND COL.Table_Schema = @schema

--Declare variables for storing the output
    @Table_name VARCHAR(100),
    @Column_name VARCHAR(100);

--Create Cursor for looping through the above table
OPEN temp_cursor 
FETCH NEXT FROM temp_cursor
INTO @Table_name,@Column_name
PRINT 'Table_Name Column_Name'

--Declare variable for storing SQL query string

--Creating SQL Query to check the search value in each table and each column from the temporary table that we created
--Here, we are creating query to search the exact value; use LIKE to find the approximate value
SET @SQL = 'SELECT '''+@Table_Name+''' AS'+'''Table_Name'''+', '''+ @Column_Name+'''AS'+'''Column_Name'''+' ,'+@Column_Name+ ' FROM '+@Table_Name+
' WHERE '+@Column_name + ' = '''+@SearchValue+''''

INSERT INTO #tempTableFinal
EXECUTE sp_executesql @SQL

FETCH NEXT FROM temp_cursor
INTO @table_name,@Column_name 

SELECT * FROM #tempTableFinal

CLOSE temp_cursor;

DEALLOCATE temp_cursor;

DROP TABLE #tempTableColumn
DROP TABLE #tempTableFinal

The query will loop through all the tables within schema “dbo” and for all the columns having datatype of “NVARCHAR” and will return Tablename along with Columnname where the value is found.

This way, we can search for a specific value having a specific datatype in the database.
Please let us know if you have any other better ideas for this, always open! 🙂

Happy T-SQL!



INKEY is your solution partner.
Our focus is to deliver you in-time intelligent innovative solutions ("key") for the problems in hand. Maintaining a quality standard right from the inception of a project is our top most priority.

Our team of talented professionals will execute your projects with dedication and excellence. We take ownership and accountability for the effort that goes into meeting our client’s needs.

Years of experience and proven success of delivering innovative custom solutions.

More posts by

Leave a Reply

Your email address will not be published.

The maximum upload file size: 2 MB. You can upload: image, audio, video, document, spreadsheet, interactive, text, archive, code, other. Drop file here

Would you like to digitize your business and put it on the cloud?
Do you need clear, concise reports for your organization?