SQL executing on multiple DB''s (but not all)?
-
Hi,
Because I'me a newbie on this... and I don't want to make a monstrous-query, please some advice on this:
In pseudo-code:
for databasename in (specified list of db names)
do
some sql code (i.e. create table xyz)
done
I've searched and found the foreachdb option, but I don't want to execute the sql n ALL db's but only the ones specified.
Any help is appreciated!
-
You can use sp_MSforeachdb to run against all dbs, and then just check the db before you run what you need. e.g.
create table #dbs (name sysname)
insert #dbs
select 'master'
union all select 'msdb'
union all select 'model'
union all select 'tempdb'
exec sp_MSforeachdb @command1 =
'if exists (select * from #dbs where name = ''?'') select ''This is database: ?'''
drop table #dbs
You can read about sp_MSforeachdb here, should you wish:
http://www.databasejournal.com/features/mssql/article.php/3441031
Clearly this method is neat, but you should take into account the warning notes at the bottom of the above link.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Hi Ryan,
Thanks for your quick reaction.
It will do the job, but it is not wat I was looking for.
I want to use a construction in which the user needs to specify in the start what items to loop for. The loop can be executing DML on tables or DBCC commands on databases.
In the start there's a list of items (databases, or tables), then I want to execute the next block on the first item (that needs to be parsed into the second block). I want to repeat the second block of code for all the items in the first.
I'm foreign so maybe my English lacks to explain what I want, SORRY!
-
Hi EA,
> It will do the job, but it is not wat I was looking for.
If it will do the job, what is the problem?
I'm afraid I don't understand what you want. I think an example would make it easier - some specific scenario you could think of that we could implement for you, and then which you could extend to do exactly what you need.
Could you provide an example?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Hi Ryan,
Example 1 - create the same table in multiple databases
For dbName in database1, database2, database3
do
use $dbName
go
create table test .......
done
Example 2 - delete records in multiple tables (the column update_date exists!)
For tableName in table1, table2
do
delete from $tableName where update_date<'01-01-2006'
done
The actions in the do ... done loop needs to be repeated for all the values specified after the 'For'.
It would be great if you can give me some hints or examples how to create it in T-SQL!
Thanks in advance.
Cheers,
Eveliene (Yes, I do have a 'normal' name
) -
Okay, Eveliene - Here's example 1...
--data
create table #dbs (name sysname, )
insert #dbs
select 'model'
union all select 'tempdb'
--calculation
exec sp_MSforeachdb @command1 =
'if exists (select * from #dbs where name = ''?'')
begin
print ''creating ?.dbo.test1''
create table ?.dbo.test1(id int, databasename sysname)
insert ?.dbo.test1 values (1, ''?'')
end'
--select results
select * from model.dbo.test1
select * from tempdb.dbo.test1
--tidy up
exec sp_MSforeachdb @command1 =
'if exists (select * from #dbs where name = ''?'')
begin
print ''dropping ?.dbo.test1''
drop table ?.dbo.test1
end'
go
drop table #dbs
/*results
creating model.dbo.test1
creating tempdb.dbo.test1
id databasename
----------- -----------------
1 model
id databasename
----------- -----------------
1 tempdb
dropping model.dbo.test1
dropping tempdb.dbo.test1
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
And here's example 2...
Note that you only really need the bits in purple.
--preparation
create table table1 (id int identity(1, 1), update_date datetime)
insert table1
select getdate()
union all select dateadd(year, -1, getdate())
create table table2 (id int identity(1, 1), update_date datetime)
insert table2
select getdate()
union all select dateadd(year, -1, getdate())
union all select dateadd(year, -2, getdate())
--calculation
exec sp_MSforeachtable @whereand = 'and name in (''table1'', ''table2'')', @command1 =
'begin
print ''? before delete:''
select * from ?
print ''deleting from ?''
delete from ? where update_date < ''20060101''
print ''? after delete:''
select * from ?
end'
--tidy up
go
drop table table1
drop table table2
/*results
[dbo].[table1] before delete:
id update_date
----------- ------------------------------------------------------
1 2006-07-13 15:37:45.223
2 2005-07-13 15:37:45.223
deleting from [dbo].[table1]
[dbo].[table1] after delete:
id update_date
----------- ------------------------------------------------------
1 2006-07-13 15:37:45.223
[dbo].[table2] before delete:
id update_date
----------- ------------------------------------------------------
1 2006-07-13 15:37:45.270
2 2005-07-13 15:37:45.270
3 2004-07-13 15:37:45.270
deleting from [dbo].[table2]
[dbo].[table2] after delete:
id update_date
----------- ------------------------------------------------------
1 2006-07-13 15:37:45.270
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
In sql 2005 edition you need to use o.name sintax
see the example:
USE WK_verify
exec sp_MSforeachtable
@command1 = 'print "Processing table ?" select * from ? where n_id = "28" ',
@postcommand = 'Print "postcommand execution" '
, @whereand = ' and o.name LIKE "nome%" '
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply