Run same SQL or Stored Proc for multiple databases

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Run same SQL or Stored Proc for multiple...

Run same SQL or Stored Proc for multiple databases

Rate Topic

Display Mode

Topic Options

Author

Message

Joe Contreras-290946

Posted Friday, March 27, 2009 6:02 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 7:15 AM
Points: 68, Visits: 181

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

Jeffrey Williams 3188

Posted Friday, March 27, 2009 6:40 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:21 AM
Points: 4,317, Visits: 9,216

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

arun.sas

Posted Friday, March 27, 2009 9:47 PM

Ten 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

Joe Contreras-290946

Posted Saturday, March 28, 2009 5:40 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 7:15 AM
Points: 68, Visits: 181

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

gloria walker

Posted Wednesday, August 25, 2010 7:35 PM

Forum 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

Joe Contreras-290946

Posted Friday, August 27, 2010 4:50 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 7:15 AM
Points: 68, Visits: 181

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

kkcarlson24

Posted Thursday, October 20, 2011 10:49 AM

Forum 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

handkot

Posted Thursday, October 20, 2011 11:45 PM

Ten Centuries

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:18 PM
Points: 1,016, Visits: 432

exec sp_MSforeachdb @command1 = 'use [?]; SELECT COUNT(*) FROM MyLeads WHERE active = 1'

Post #1194124

Sergiy

Posted Friday, October 21, 2011 2:34 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:47 AM
Points: 4,550, Visits: 8,201

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

Joe Contreras-290946

Posted Friday, October 21, 2011 3:59 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 7:15 AM
Points: 68, Visits: 181

Thanks Everyone!!

Post #1194232

« Prev Topic | Next Topic »

Permissions