asked on

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.

Raja Jegan R

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

JoeMiskey

ASKER

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

Raja Jegan R

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..

JoeMiskey

ASKER

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?

wdosanjos

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

Raja Jegan R

JoeMiskey,

then you need to use the Cursor approach mentioned by wdosanjos to get it work..
No other go..

JoeMiskey

ASKER

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?

wdosanjos

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

JoeMiskey

ASKER

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...

JoeMiskey

ASKER

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

wdosanjos

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')

JoeMiskey

ASKER

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

ASKER CERTIFIED SOLUTION

wdosanjos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JoeMiskey

ASKER

Beautiful! I can't thank you enough!