Searching one value in all the tables in one Database 5

[redpotato]

Hello guys,

maybe it sounds crazy but I would like to search one value which might exist in certain tables but there are like thousands tables exist in a DB.
how could I search that in one shot?

one more thing, how can I get all table names in one DB with Stored Procedure?

Thanks for your help!

 

[markros]

To get the table names:

select Table_Name from Information_Schema.Tables

To solve your problem, check

http://blogs.lessthandot.com/index..../the-ten-most-asked-sql-server-questions--1#2

PluralSight Learning Library

 

[SQLBill]

There is an undocumented procedure that should work for you.

exec sp_MSForEachTable
 @command1 = 'command'

Example:

exec sp_MSForEachTable
  @command = 'SELECT lname FROM ? WHERE city = ''Pittsburgh'''

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[redpotato]

Thanks for the help.
but when I ran the command below, it gives me Could not find stored procedure 'sp_MSForEachTable'

exec sp_MSForEachTable
@command = 'SELECT item FROM ? WHERE item = ''58I'''

did you mean to create sp_MSForEachTable stored procedure?

 

[redpotato]

sorry, i found out how to run the sp.
but now i get this message below,

Server: Msg 8145, Level 16, State 2, Procedure sp_MSforeachtable, Line 0
@command is not a parameter for procedure sp_MSforeachtable.

 

[jbenson001]

Try this:

EXEC sp_msForEachTable 'SELECT * FROM ?  WHERE item = ''58I'' '

 

[SQLBill]

I left off the number 1 in my example...it has to be:

@command1

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[redpotato]

thanks guys,

I tried jbenson001's command and it works!
and SQLBill's command worked as well!!

Thank you guys!!

 

[jbenson001]

Glad to help

 

[SQLBill]

jbenson001's worked since there was only one command. I should have included more information...but if you ever need to use it again, you can include up to three commands by using:
@command1 =
@command2 =
@command3 =
There are some other options also, you should be able to find them via a search.

You can also do the same thing for every database with: sp_MSForEachDB

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[markros]

Here is a good link for sp_MSForEachTable and sp_MSForEachDB

http://www.mssqltips.com/tip.asp?tip=1905&ctc

PluralSight Learning Library