Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Run same SQL or Stored Proc for multiple databases Expand / Collapse
Author
Message
Posted Friday, March 27, 2009 6:02 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 12:43 PM
Points: 75, Visits: 203
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,
Post #685467
Posted Friday, March 27, 2009 6:40 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:12 PM
Points: 4,386, Visits: 9,502
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #685477
Posted Friday, March 27, 2009 9:47 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,231, Visits: 3,483
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
Post #685493
Posted Saturday, March 28, 2009 5:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 12:43 PM
Points: 75, Visits: 203
Unfortunately this is on a SQL Server 2000 environment. However I'll definitely give the cursor option a try.

Thank You very much
Post #685538
Posted Wednesday, August 25, 2010 7:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 16, 2010 8:42 PM
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.
Post #975300
Posted Friday, August 27, 2010 4:50 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 12:43 PM
Points: 75, Visits: 203
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.
Post #976836
Posted Thursday, October 20, 2011 10:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 20, 2011 1:46 PM
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.


Post #1193893
Posted Thursday, October 20, 2011 11:45 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 10:01 PM
Points: 1,301, Visits: 466
exec sp_MSforeachdb @command1 = 'use [?]; SELECT COUNT(*) FROM MyLeads WHERE active = 1'

Post #1194124
Posted Friday, October 21, 2011 2:34 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:23 PM
Points: 4,576, Visits: 8,341
Create "sp_" procedure in master database.
Put your code in there.
Then use handkot's approach to execute that procedure for each database.
Post #1194186
Posted Friday, October 21, 2011 3:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 12:43 PM
Points: 75, Visits: 203
Thanks Everyone!!
Post #1194232
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse