Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2006
    Posts
    5

    Unanswered: Execute sql on multiple objects (DB's or tables)

    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 objectname in (specified list of objects)
    do
    some sql code (i.e. create table xyz)
    done

    With 'objects' I mean a database or table name.

    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!

  2. #2
    Join Date
    Nov 2003
    Posts
    167
    sticking to your '(list of objects)' syntax, you could do this:

    Code:
    declare @x int
    declare @dbname varchar(500)
    set @x = 1
    
    create table #databases
    (
    ID int IDENTITY,
    name varchar(500)
    )
    
    insert #databases
    select name
    from master..sysdatabases
    where name in(<your list of databases separated by comma>)
    
    
    while @x <= (select max(id) from #databases)
    begin
         select @dbname = name from #databases where id = @x
    	--<capture @dbname for dynamic sql>
        print @dbname
    	set @x = @x + 1
    end
    
    drop table #databases
    Good luck.
    Kit Lemmonds

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •