Blogs

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

Inkey, September 2, 2022 29441 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 on Microsoft SQL Server.

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.

Discover the power of our services:

Microsoft Fabric, Power BI, Microsoft Business Intelligence, SQL Server, and Business Central. By the power of these services, from advanced analytics to seamless business integration, we’ve got the expertise you need to optimize operations and drive growth. Harness the potential of your data infrastructure with our comprehensive suite of solutions.

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. While this query is for Microsoft SQL Server, the core of it remains the same for other databases also:

--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 on Microsoft SQL Server.
Please let us know if you have any other better ideas for this, always open! 🙂

Happy T-SQL!