Logo
banner

Blogs

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

, September 2, 2022 582 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
DECLARE @DataType VARCHAR(50) = 'NVARCHAR'

--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 
SELECT 
    CONCAT(COL.Table_Schema,CONCAT('.',QUOTENAME(COL.TABLE_NAME))) AS TABLE_NAME,
    COL.COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.COLUMNS COL
WHERE 
    Data_Type IN (@DataType)
AND COL.Table_Schema = @schema


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


--Create Cursor for looping through the above table
DECLARE temp_cursor CURSOR FOR
SELECT 
    TABLE_NAME,
    COLUMN_NAME
FROM 
    #tempTableColumn 
 
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
DECLARE @SQL NVARCHAR(max);  

WHILE @@FETCH_STATUS = 0
BEGIN
--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+''''


PRINT @SQL
INSERT INTO #tempTableFinal
EXECUTE sp_executesql @SQL
 

FETCH NEXT FROM temp_cursor
INTO @table_name,@Column_name 
END

 
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!

mm

Inkey

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