Loop Through All Tables in SQL 2005 Database

We have a database containing a couple hundred tables.  About half of these tables have a field in them called "Company_Tax_ID".  I am looking to return a list of all tables that have records where the "Company_Tax_ID" field is equal to some static value that I am looking for, (i.e. "ABC123XYZ").

I really do not care how many records in each table contain this value in the "Company_Tax_ID" field, I just need to return a list of table names that have at least one.  However, if it is easier to list all table names and the count of records containing this value for "Company_Tax_ID", that is fine too.

I did a search, and most matches were about looping within one table.  I found some other hits that looked a little promising, but they weren't quite doing what I need them to do.  

Thanks.
JoeMiskeyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
This would do:
create table #temp ( table_name varchar(50), cnt int);
insert into #temp ( table_name, cnt)
exec sp_MSforeachtable ' select ''?'' table_name, count(*) cnt from ? where Company_Tax_ID = ''ABC123XYZ'' '

Open in new window

0
JoeMiskeyAuthor Commented:
That seems to be returning errors, it looks like their is an unbalanced quotation Mark.

I tried to modify your post, accordingly.  Here is what I have:
create table Cust_No_Repl_Tax_ID_Count ( table_name varchar(50), cnt int);
insert into Cust_No_Repl_Tax_ID_Count ( table_name, cnt)
exec sp_MSforeachtable ' select ''?'' table_name, count(*) cnt from ? where company_tax_id='ABC123XYZ'

Open in new window

0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Fixed it below:

create table Cust_No_Repl_Tax_ID_Count ( table_name varchar(50), cnt int);
insert into Cust_No_Repl_Tax_ID_Count ( table_name, cnt)
exec sp_MSforeachtable ' select ''?'' table_name, count(*) cnt from ? where company_tax_id=''ABC123XYZ'' '

Note: Additional quotes are required since single quotes is encapsulated within quotes..
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

JoeMiskeyAuthor Commented:
Well, it seem to accept that OK, but I experienced errors when I tried to run it.  Basically, it gave me errors on the tables that do not contain the "Company_Tax_ID" field (as I said, about half the tables contain the a field named "Company_Tax_ID", but the other half don't).

I thought maybe it would just return these errors to the screen, but process the rest, but after it finished, there are no records in my table.  It appears that these errors are preventing ANY records from being written (and I did confirm that there are tables with the value I am looking for).

Any idea on how to get it to ignore these errors and write records to the table?
0
wdosanjosCommented:
Please try the following:

set nocount on
create table ##temp (tbl varchar(4000))

declare csr cursor for 
SELECT DISTINCT [table_schema], [table_name]
  FROM [INFORMATION_SCHEMA].[COLUMNS] 
 WHERE UPPER(COLUMN_NAME) = UPPER('Company_Tax_ID')
 
declare @scm varchar(128), @tbl varchar(128)
 
open csr

fetch csr into @scm, @tbl

while @@FETCH_STATUS = 0 begin
	exec ('insert into ##temp select distinct '''+@tbl+''' tbl from [' + @scm + '].['+@tbl+'] where Company_Tax_ID = ''ABC123XYZ''')
	fetch csr into @scm, @tbl
end

select * from ##temp

close csr
deallocate csr
drop table ##temp

Open in new window

0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
JoeMiskey,

then you need to use the Cursor approach mentioned by wdosanjos to get it work..
No other go..
0
JoeMiskeyAuthor Commented:
Thanks both for your replies.  I tried the cursor approach, and I am getting an error message that I do not know what it means.

It says "Conversion failed when converting the nvarchar value 'PI' to data type int".

I cannot tell what table or field is returning this error (or exactly why).  Any ideas?
0
wdosanjosCommented:
I think Company_Tax_ID is defined as an int in some table.  Please try the following:

set nocount on
create table ##temp (tbl varchar(4000))

declare csr cursor for 
SELECT DISTINCT [table_schema], [table_name]
  FROM [INFORMATION_SCHEMA].[COLUMNS] 
 WHERE UPPER(COLUMN_NAME) = UPPER('Company_Tax_ID')
 
declare @scm varchar(128), @tbl varchar(128)
 
open csr

fetch csr into @scm, @tbl

while @@FETCH_STATUS = 0 begin
	exec ('insert into ##temp select distinct '''+@tbl+''' tbl from [' + @scm + '].['+@tbl+'] where CAST(Company_Tax_ID as nvarchar(100)) = ''ABC123XYZ''')
	fetch csr into @scm, @tbl
end

select * from ##temp

close csr
deallocate csr
drop table ##temp

Open in new window

0
JoeMiskeyAuthor Commented:
I got another similar type of error saying "Conversion failed when converting the nvarchar value 'AC' to data type int".  The interesting thing about that is that "AC" and "PI" are status values that are stored in a whole different in a few of the tables.  I am not sure why it would be picking those up.

There are a number of "Custom" and temporary tables in the database.  I wonder if one of those has a problem and is causing those errors.  I do not really care about those tables, so I added criteria to the WHERE clause to exclude table names that start with some pre-defined prefixes that we use.  So far so good.  It has been running about 20 minutes now and has not errored out (the last two example errored out before 2 minutes).

I will let you know if it is successful when finished...
0
JoeMiskeyAuthor Commented:
OK, the job has been running for over 2 hours know, and hadn't finished.  So I took a peek at the contents of the temp table, and noticed that it is running against views in addition to tables.  Since there are a ton of views, this is taking forever.  Luckily, most of our views are prefixed with "qry" or "eqry".  So I excluded them from the code, and then re-ran it and it finished in under 2 minutes!!!

So it looks like it works!  Just out of curiosity though, is there something that can be added to the code that will limit it to only run against tables and not views?

Thanks
0
wdosanjosCommented:
Yes, you can change the SELECT as follows:

SELECT DISTINCT C.[table_schema], C.[table_name]
  FROM [INFORMATION_SCHEMA].[COLUMNS] C
    INNER JOIN [INFORMATION_SCHEMA].[TABLES] T ON
               T.[table_catalog] = C.[table_catalog] AND
               T.[table_schema] = C.[table_schema] AND
               T.[table_name] = C.[table_name] AND
 WHERE UPPER(C.COLUMN_NAME) = UPPER('Company_Tax_ID')

0
JoeMiskeyAuthor Commented:
You have an extra "AND" before the "WHERE" clause.  I removed that and tried re-running, and that still picks up the Views.  So that doesn't appear to restrict it just to running against Tables.

It is not critical, as due to our naming convention, I can exclude 95% of the views in teh database in the WHERE clause.  I just thought if there was an easy way to restric this code to just run against tables, it might be useful for future use.

Thanks
0
wdosanjosCommented:
Sorry, I missed one crucial line when I copied / pasted:

SELECT DISTINCT C.[table_schema], C.[table_name]
  FROM [INFORMATION_SCHEMA].[COLUMNS] C
    INNER JOIN [INFORMATION_SCHEMA].[TABLES] T ON
               T.[table_catalog] = C.[table_catalog] AND
               T.[table_schema] = C.[table_schema] AND
               T.[table_name] = C.[table_name] AND
               T.[table_type] = "BASE TABLE"
 WHERE UPPER(C.COLUMN_NAME) = UPPER('Company_Tax_ID')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JoeMiskeyAuthor Commented:
Beautiful!  I can't thank you enough!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.