Sunday , 25 June 2017
Home » SQL » Searching all tables in sql server

Searching all tables in sql server

Searching all tables in sql server

Imagine a case where you want to see on which table there is a value or string of  ‘Barlet’ and you have 50+ tables on your database. Searching all tables in sql server one by one will be a huge pain and a waste of time. In this article you will see how to search all tables in your current database and see whether the value or string exists.

Since SQL Server doesn’t has a built-in function or stored procedure for this purpose, you need to create your own stored procedure. Copy and paste the script below to create stored procedure for searching tables for a string.

The script below is taken from http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Script for searching all tables in sql server

After you have created the stored procedure above you need to execute with a  parameter. Type the query below to execute the stored procedure with searching parameter ‘Barlet’. After you execute this procedure it will search all the tables in the current database and check where the value ‘Barlet’ exists.
Searching all tables in sql server

As you can see in the picture the value ‘Barlet’ exists on my Persons  and qq tables.
Search speed depends on your database. The bigger database is the slower the search will be.

Check Also

What is a Stored Procedure

What is a Stored Procedure What is a Stored Procedure? Stored procedures are a group …

Leave a Reply

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