Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Run same SQL or Stored Proc for multiple databases


Run same SQL or Stored Proc for multiple databases

Author
Message
Joe Contreras-290946
Joe Contreras-290946
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 206
Hi,
I have a series of SQL that are the same that need to run on multiple databases

SELECT COUNT(*) FROM MyLeads WHERE active = 1



I have 70-100 databases that need to run this same SQL, how can I do this once for all databases.

The design is very unusual and I just need to figure out how to run the same SQL for multiple databases.

Thanks,
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4463 Visits: 9829
I can think of a couple options:

1) Install SQL Server 2008 client tools, setup a folder and register your servers, then use the multi-script option to open a connection to all instances and run your query.

2) Install SQL Server 2005/2008 client tools and use SQLCMD and a script file to loop through all servers and run the query.

3) Install Windows Powershell and use a powershell script to loop through all instances and run the query (using SQLCMD or OSQL).

4) Use wscript/cscript and SMO

5) Look at Redgate tools - they have one that allows for running queries on multiple instances.

6) Other tools like the Redgate tools that allow multi-select queries.

7) Install SQL Server 2005/2008 and use SSIS to build a package that loops through all instances. This is more of an ETL tool though - but can work very well.

8) Use DTS to build a package

There are probably more - but I think that will get you started. Which one you choose will really depend on how often this needs to be done and whether or not it needs to be automated.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

arun.sas
arun.sas
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1237 Visits: 3493
Hi,

try this

DECLARE AllDatabases CURSOR FOR
SELECT name FROM master.dbo.sysdatabases WHERE dbid > 4

OPEN AllDatabases

DECLARE @DBNameVar NVARCHAR(128),@Statement NVARCHAR(300)

FETCH NEXT FROM AllDatabases INTO @DBNameVar
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT N'CHECKING DATABASE ' + @DBNameVar
SET @Statement = N'USE ' + @DBNameVar + CHAR(13)
+ N'SELECT COUNT(*) FROM MyLeads WHERE active = 1'
EXEC sp_executesql @Statement
PRINT CHAR(13) + CHAR(13)
FETCH NEXT FROM AllDatabases INTO @DBNameVar
END

CLOSE AllDatabases
DEALLOCATE AllDatabases


ARUN SAS
Joe Contreras-290946
Joe Contreras-290946
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 206
Unfortunately this is on a SQL Server 2000 environment. However I'll definitely give the cursor option a try.

Thank You very much
gloria walker
gloria walker
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 8
I'm running SQl Server 2000 and I get the following error message:
Msg 402, Level 16, State 1, Line 12
The data types nvarchar and ntext are incompatible in the add operator.

I changed the SET @Statement to my sql script, which is a much larger file.
Joe Contreras-290946
Joe Contreras-290946
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 206
Hi,

Try this function to loop through all databases

--This query will return a listing of all tables in all databases on a SQL instance:
DECLARE @command varchar(1000)
SELECT @command = 'USE ? SELECT name FROM mycustomtable WHERE name = ''Foo'' ORDER BY name'
EXEC sp_MSforeachdb @command

This will run the SQL command for each database containing mycustomtable.

Hope this helps.
kkcarlson24
kkcarlson24
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 3
you can do the following:

use master

declare @dbname varchar(100)
,@sql varchar(max)


create table #TempDBs (
dbname nvarchar(100)
, RecordCount int
)

declare db_cur cursor for
SELECT name
FROM master.dbo.sysdatabases where dbid>4

open db_cur
fetch next from db_cur into @dbname

while @@FETCH_STATUS = 0
begin

set @sql='insert into #TempDbs(
dbname,
RecordCount
)
select '''+@dbname+''' DbName, COUNT(*) count FROM '+@dbname+'.dbo.MyLeads WHERE active = 1'
exec(@sql)


fetch next from db_cur into @dbname

end

close db_cur
deallocate db_cur

select * from #TempDBs order by DbName

drop table #TempDBs

To make the results cleaner, I inserted into a temp table, then queried the temp table.
handkot
handkot
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1894 Visits: 569
exec sp_MSforeachdb @command1 = 'use [?]; SELECT COUNT(*) FROM MyLeads WHERE active = 1'


Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5832 Visits: 11403
Create "sp_" procedure in master database.
Put your code in there.
Then use handkot's approach to execute that procedure for each database.
Joe Contreras-290946
Joe Contreras-290946
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 206
Thanks Everyone!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search