Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
btamulis
Yak Posting Veteran
64 Posts |
 Posted - 2008-08-27 : 17:44:02
|
| Greetings....Is there a script available to search a database for all tables that store a specific value? Example - 30 tables have a column - ITEMNMBR. What a need to know is how many of the 30 tables store a specific value - let's say ITEMNMBR = 'FINISHED GOOD A'.I have a script that will list all tables with a column of ITEMNMBR -now I need to know a specific Item Number.Any thoughts?Thanks in advance, this forum is usually pretty helpful for me. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-27 : 17:50:47
|
| Run this to generate the queries you'll need to run:SELECT 'SELECT * FROM ' + TABLE_NAME + ' WHERE ITEMNMBR = ''FINISHED GOOD A'''FROM INFORMATION_SCHEMA.COLUMNSWHERE COLUMN_NAME = 'ITEMNMBR'Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
btamulis
Yak Posting Veteran
64 Posts |
Posted - 2008-08-27 : 18:07:45
|
| Fantastic......this gives me exactly what I was looking for.....Absolutely brilliant!The learning point is that the information_schema.columns is used by SQL to keep track of all the tables with ITEMNMBR column? Correct?Thanks again.... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-27 : 18:10:07
|
| There are a bunch of INFORMATION_SCHEMA views to query for the schema. You should check them out in the master database and read up on them in SQL Server Books Online. I typically use TABLES and COLUMNS from those views.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
Finding All tables where a data element is stored