May 13, 2008 at 4:09 pm
I have a table... it's name is contacts, it exists in 1816 databases hosted for my company's various clients.
I need all the records where the lastname field in the contacts table contains _TEST DATABASE_
Short of a looping cursor through sys.databases that looks something like this, can anybody think of another way to query all 1800+ databases for this particular record?
select
database_name = db_name(),
*
into #contacts
from contacts
where 1=0
select
database_name = [name]
into #databases
from sys.databases where database_id > 4 order by [name];
SET NOCOUNT ON;
truncate table #contacts;
declare @db varchar(256);
declare @Row_Count int;
declare @err_count int;
declare @cmd varchar(max);
declare @counter int;
set @counter = 0;
set @err_count = 0;
declare csr_db cursor for select * from #databases;
open csr_db;
fetch next from csr_db into @db;
while @@fetch_status != -1
BEGIN
set @counter = @counter + 1;
set @cmd = 'declare csr_exists cursor for select count(*) from [' + cast(@db as varchar) + '].sys.tables where [type] = ''U'' and [name] = ''contacts''';
exec(@cmd);
open csr_exists;
fetch next from csr_exists into @Row_Count;
close csr_exists;
deallocate csr_exists;
if @Row_Count >= 1
BEGIN
print @db;
set @cmd = 'insert into #contacts select ''' + cast(@db as varchar) + ''',* from [' + cast(@db as varchar) + '].dbo.contacts where lastname = ''_TRAINING DATABASE_''';
exec(@cmd);
END;
fetch next from csr_db into @db;
WHILE @@fetch_status = -2
BEGIN
fetch next from csr_db into @db;
END;
END;
close csr_db;
deallocate csr_db;
print cast(@counter as varchar) + ' databases checked';
select * from #contacts
May 13, 2008 at 4:46 pm
Can't think about something other than cursor, but instead of executing the code within the cursor, you could use it to generate code into text output. Then you copy the result and execute it like normal sql.
I use this approach when I generate script synchronizing all tables in a database from linked server.
Piotr
...and your only reply is slàinte mhath
May 14, 2008 at 7:29 am
So basically store the queries as
INSERT INTO #contacts
SELECT [databasename],* FROM [databasename].dbo.contacts WHERE lastname = '_TRAINING DATABASE_'
UNION ALL
and just append all the SELECTS into a single statement?
I can see how that would run a bit faster...
I should note that after I posted this, I found that one database (there's always one huh) has a different structure for the contacts table because it's at an earlier version of the database. So I am stuck with the one table at a time looping cursor I guess.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply