Inkey Solution Logo
banner

Blogs

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

, September 2, 2022 34752 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!

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

4 responses to “Microsoft SQL Server > Find a specific value in all the tables of a database using T-SQL”

  1. Karol says:

    The procedure doesn’t work if there is a space (‘ ‘) in the column name. To fix that it is enough to add square brackets (‘[‘, ‘]’) to line 67 and 68 like below

    SET @SQL = ‘SELECT ”’+@Table_Name+”’ AS’+”’Table_Name”’+’, ”’+ @Column_Name+”’AS’+”’Column_Name”’+’ ,[‘+@Column_Name+ ‘] FROM ‘+@Table_Name+
    ‘ WHERE [‘+@Column_name + ‘] = ”’+@SearchValue+””

  2. What are some potential limitations or considerations when using the provided SQL query to search for a specific value in a database?

  3. Admin says:

    Thanks for bringing this up Karol. I agree with you on this. In fact, the same tip is true even for the Table Name of any keyword we use in T-SQL.

  4. Admin says:

    Hello Luqmaan,

    There could be some limitations/considerations with the T-SQL. The idea of sharing this was just to show a path to the readers on how this could be achieved. Now, based on individual use case, the T-SQL shared here could be modified to give the expected result.

Leave a Reply

Your email address will not be published. Required fields are marked *

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?