April 13, 2010 at 2:17 pm
Hi All,
I want to find few records which exist in one of the tables of the database. Database has about 200 tables and these records exist in one of these tables. I do not want to manually go to all these tables and check one by one, it is time consuming. For example I have TestId 1000 which I want to find from all the tables in one particular database. All the tables do have a column called TestId.
Any help is appriciated.
Thanks in advance.
April 13, 2010 at 2:33 pm
Basically you can use the stored procedure sp_msforeachtable and pass in a "SELECT * where TESTID = xxx" for this. you'll get a series of result sets, one for each table.
However this typically isn't the type of query people run. Tables store different types of data. If you tell us what you are trying to accomplish, maybe we have another idea.
April 13, 2010 at 2:35 pm
Thanks for the quick reply.
I have to find few TestIds which are stored in one of the tables out of this 100 tables. All the tables have the same column called TestId under which they are stored. So, once I know which table they are stored in I can fetch them from there and use it where I want to use them. does this help?
April 13, 2010 at 2:40 pm
If you elect to follow Steve's forume posting (above this one)
use this link to watch a video that shows you how to use the sp_Sp_MSforeachtable undoucumented procedure
Using SP_MSForEachDB and SP_MSForEachTable - SQL School Video
By Andy Warren, 2009/03/26
April 13, 2010 at 3:16 pm
That doesn't really help. For example, are you looking for the test itself? Someone's answers? The data on the test? There are any number of things that relate to a testID. Searching for all of them doesn't sound like something you need to do, unless you are mapping the database.
What I mean is, what does a testid mean to you? And what are you looking for? If every table has testid, you need, or likely should, be narrowing down what data you want, what information. Not just everything about testid 1.
April 13, 2010 at 4:07 pm
well, each table has 5 fields. one of the field is called TestID. In this field there are records like 100, 200, 1001, 4000, 20034, 49595 etc. It is an integer field. I want to find that which table exactly contains a record of my wish. I don't want to run select query on each of these tables to find a record of my wish.
e.x. TestId = 100
I want to know that where does this record exist. I mean in which table I can find TestId = 100 out of 100 tables.
April 13, 2010 at 4:10 pm
would this work?
exec sp_msforeachtable "select * from ? where TestId = 100"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply