?
Solved

How to search all tables in a database using SQL Server Management Studio?

Posted on 2008-11-04
12
Medium Priority
?
601 Views
Last Modified: 2012-06-22
I'm trying to find an easy way to search though every table in a database to find instances of a particular field value (regardless of column name).  I'm trying to find all instances of a particular invoice number.  The column headings will be different in different tables.

I am having a hard time tracking an sql query online to do this.
0
Comment
Question by:bradlee27514
  • 5
  • 5
  • 2
12 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 22880008
0
 
LVL 13

Expert Comment

by:sm394
ID: 22880040
0
 
LVL 13

Expert Comment

by:sm394
ID: 22880050
sorry chapmandew :)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 60

Expert Comment

by:chapmandew
ID: 22880064
;)  no problem at all..its a good one.  
0
 

Author Comment

by:bradlee27514
ID: 22881432
i've never created a stored procedure.  would i just go to the database, right click and go to "new query"  sorry if this is a dumb question, i've just never created one before.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22881607
yep, that is all you need to do...
0
 

Author Comment

by:bradlee27514
ID: 22886118
wow that's awesome, anyway to get it to only return exactly that string?  Its including results where the string is part of the whole field value.  For example searching for '0000031' also yields 'abc00000031'.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22886158
try this instead  ' 0000031'
0
 

Author Comment

by:bradlee27514
ID: 22886373
Confused, that is what i'm doing.
EXEC SearchAllTables '0000031'
GO

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22886385
EXEC SearchAllTables ' 0000031'
GO
0
 

Author Comment

by:bradlee27514
ID: 22886519
Sorry, i didn't see the space.  that yields 0 results, should be at least 1 results
0
 

Author Closing Comment

by:bradlee27514
ID: 31513258
thanks so much!  it'd be nice to further refine, but  this definitely works.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question