Run SQL script against an array of databases 2

[mlowe9]

Please let me start by saying I really don't know the syntax for SQL scripts when it comes to variables, loops, etc., so my example code I'm sure isn't close to what I need.

Could someone share with me the syntax to run a SQL script against an array/list of databases. So instead of doing:

USE DB1
select count(*) from users
USE DB2
select count(*) from users
USE DB3
select count(*) from users

I could do something along the lines of

MyDB(1) = 'DB1'
MyDB(2) = 'DB2'
MyDB(3) = 'DB3'

Loop I = 1 to 3
  USE MyDB(I)
  select count(*) from users
end loop

 

[markros]

Look into

sp_MSForEachDB and sp_MSForEachTable

http://www.databasejournal.com/feat...1/SPMSFOREACHDB---Databases-List-Via-TSQL.htm

 

[gmmastros]

or this...

Select Sum(AliasName.UserCount) As TotalUserCount
From   (
       select count(*) As UserCount from DB1.dbo.users
       Union All
       select count(*) from DB1.dbo.users
       Union All
       select count(*) from DB1.dbo.users
       ) As AliasName

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

George,

I guess you meant -

Select Sum(AliasName.UserCount) As TotalUserCount
From (
select count(*) As UserCount from DB1.dbo.users
Union All
select count(*) from DB2.dbo.users
Union All
select count(*) from DB3.dbo.users
) As AliasName

 

[gmmastros]

right. I forgot to change the database name in the unions. Thanks.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mlowe9]

Thanks guys. Still not quite what I'm hoping for. The sp_MSForEachDB seems to be closest to what I'm wanting, except that I'd like to be able to define the databases I run the script on, instead of running it on ALL databases. There may be 10 databases on the server, but I only need to run the script on 3 (just as an example).

I do appreciate the input.

 

[markros]

Alternatively you can generate script as a string and then run it. I used VFP with textmerge for this when I needed something similar, but you can do this in SQL Server directly, of course.

 

[snitin78]

Try this:

declare @db_name_table table (dbname varchar(80))

-- Put one insert statement for each of your DB here
insert into @db_name_table values ('DB1')
insert into @db_name_table values ('DB2')

declare @my_cursor cursor

declare @db_name varchar(80),
@sql varchar(255),
@table_name varchar(80)

set @table_name = 'users'

set @my_cursor = cursor for select * from @db_name_table
open @my_cursor
fetch next from @my_cursor into @db_name
while(@@fetch_status = 0)
begin
set @sql = 'select top 1 * from ['+@db_name+'].[dbo].['+@table_name+']'

exec(@sql)

fetch next from @my_cursor into @db_name
end

 

[mlowe9]

That's exactly what I was wanting. Thank you so much.

 

[mlowe9]

Can I suppress the "(1 row(s) affected)" message caused by "insert into @db_name_table" lines?

I only get it on text output, but that's what I prefer. Not a big deal, just wondering if there's an easy fix.

 

[markros]

SET NOCOUNT ON;

 

[snitin78]

Just put 'set nocount on' at the top of your SQL.

 

[mlowe9]

Outstanding. You guys are awesome!