March 27, 2009 at 6:02 pm
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,
March 27, 2009 at 6:40 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 27, 2009 at 9:47 pm
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
March 28, 2009 at 5:40 am
Unfortunately this is on a SQL Server 2000 environment. However I'll definitely give the cursor option a try.
Thank You very much
August 25, 2010 at 7:35 pm
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.
August 27, 2010 at 4:50 pm
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.
October 20, 2011 at 10:49 am
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.
October 20, 2011 at 11:45 pm
exec sp_MSforeachdb @command1 = 'use [?]; SELECT COUNT(*) FROM MyLeads WHERE active = 1'
I Have Nine Lives You Have One Only
THINK!
October 21, 2011 at 2:34 am
Create "sp_" procedure in master database.
Put your code in there.
Then use handkot's approach to execute that procedure for each database.
_____________
Code for TallyGenerator
October 21, 2011 at 3:59 am
Thanks Everyone!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply