-1

Is there a query for SQL Server to find a specific value in all tables and databases? I emphasize query not stored procedure.

Mohammad Reza Shahrestani
  • 1,119
  • 3
  • 17
  • 28
M.h Basiri
  • 56
  • 9
  • 2
    How come you want to do this? Don't you know where you store your data? – jarlh Apr 20 '17 at 07:39
  • I want to do this because if I forgot where my data is stored or whether I added a table in a big project or not – M.h Basiri Apr 20 '17 at 07:43
  • 2
    Tough luck. Simply search the tables, one at the time. (Can be done dynamically, with a loop.) – jarlh Apr 20 '17 at 07:47
  • you are right but sometimes we don't have time for this , do you know the script for this ? – M.h Basiri Apr 20 '17 at 07:49
  • 2
    Learn how to prioritize. If you just throw tables and data into your project without knowing what you're doing, you'll have to pay sooner or later. – jarlh Apr 20 '17 at 07:52
  • Thanks for your advice I'll keep it in mind – M.h Basiri Apr 20 '17 at 07:54
  • 1
    Do you ever go around searching your entire house if you know you've left your keys on the kitchen counter? Don't do that with your applications either, even in testing. Take note of @jarlh 's advices and learn to set best practices! If your database grows to hundreds of GB / TB then knowing some information in advance (*table names*) can save you hours of query search time (not to mention resources). – Radu Gheorghiu Apr 20 '17 at 08:24
  • This is the answer I posted as an answer in a Question http://stackoverflow.com/questions/43385508/find-a-value-and-shows-me-database-table-column-primary-key-of-the-found-value/43630272#43630272 – M.h Basiri Apr 26 '17 at 10:43

2 Answers2

4

Actually, there is a way.

Option 1:

There is a procedure called sp_msForEachTable that can help you. It's not really an officially documented feature and therefore not recommended to use, but it works.

EXEC sp_msForEachTable 'SELECT TOP 10 * FROM ?'

Bear in mind it might take a while to list everything if you have a lot of tables. And if you have a reeeeally lot of tables and not so much RAM, your Management Studio might crash before finishing.

Option 2:

You can use system view sys.tables to create your SQL dynamically

SELECT 'SELECT TOP 10 * FROM ' + name FROM sys.tables

Then you can copy/paste results in another window and maybe run them in parts.

Nenad Zivkovic
  • 18,221
  • 6
  • 42
  • 55
-2

If you know your table names:

SELECT * FROM table1, table2, ...;
neer
  • 4,031
  • 6
  • 20
  • 34
Lama
  • 159
  • 1
  • 7