September 14, 2011 at 9:32 am
Hi Guys! I am CM Professional and Worked in SQL Server as the CM guys should do. My Application is with Huge database architecture and there are Multiple DBs attached on one SQL Server.
Problem!
There are more than 100 DBs are attached on our SQL Server. I need to execute Scripts on all DB. What i am doing now is Executing scripts manually and this mechanical effort take time and definately cost.
Expectation:
Pls suggest me any Tool , Way , Automated Script that easily reduce my machanical work with No error. If there would be errors while running scripts ; it can easily creat file of errors for me.
Kindly help me in this regard.
Waseem Bukhari
CM Professional
September 14, 2011 at 9:38 am
I use MultiScript from RedGate Software. It works great. I use it to run against multiple databases as well as against muiltiple servers.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
September 14, 2011 at 9:43 am
There are a few ways to handle this.
The easy way is to use SQLCMD and a little scripting to connect to each database in turn and run a script.
One is use Powershell and a list of servers/databases to execute a script. A little complex, but not too bad if you mess around with it. Here's a basic example: http://blogs.technet.com/b/heyscriptingguy/archive/2010/12/30/learn-how-to-run-powershell-scripts-against-multiple-computers.aspx
The Central Management Server, if you can set one up, might work for you. It could be tricky to get it to run against multiple databases on the same server, but you might come up with something. Express can be used as a CMS: http://www.sqlservercentral.com/blogs/kendalvandyke/archive/2009/4/20/use-sql-2008-express-as-a-central-management-server.aspx
Red Gate Software sells multi-script, which has some bells and whistles to make this easier. Not sure it's worth the cost by itself, but with one of the bundles, it's a nice tool to have: http://www.red-gate.com/products/dba/sql-multi-script/
Disclosure: I work for Red Gate Software.
Also, depending on what you're trying to do, replication could be an option. If this is deployment of changes, replication from a parent db of tables/procs/etc to subscribing dbs can work.
September 14, 2011 at 9:47 am
sjimmo (9/14/2011)
I use MultiScript from RedGate Software. It works great. I use it to run against multiple databases as well as against muiltiple servers.
+1 - I also advocate this tool from RedGate.
Depending on the script you wish to run, you could try:
sp_msforeachdb
There are limitations and caveats for sp_msforeachdb to be wary of though...http://www.sqlservercentral.com/Forums/Topic438415-266-1.aspx
gsc_dba
September 14, 2011 at 9:50 am
gsc_dba (9/14/2011)
Depending on the script you wish to run, you could try:
sp_msforeachdbThere are limitations and caveats for sp_msforeachdb to be wary of though...http://www.sqlservercentral.com/Forums/Topic438415-266-1.aspx
That's why I wrote a replacement[/url].
-- Gianluca Sartori
September 14, 2011 at 9:51 am
You can also use a cursor and dynamic sql like so
DECLARE @database nvarchar(100),
@sql nvarchar(max)
DECLARE c_Databases CURSOR LOCAL FOR SELECT name from master.sys.databases
OPEN c_Databases
WHILE (1=1)
BEGIN
FETCH NEXT FROM c_Databases
INTO @database
IF @@FETCH_STATUS < 0
BREAK
set @sql ='select top 10 * from ['+@database+'].sys.tables'
exec sp_executesql @sql
END
CLOSE c_Databases
DEALLOCATE c_Databases
September 14, 2011 at 10:01 am
Gianluca Sartori (9/14/2011)
gsc_dba (9/14/2011)
Depending on the script you wish to run, you could try:
sp_msforeachdbThere are limitations and caveats for sp_msforeachdb to be wary of though...http://www.sqlservercentral.com/Forums/Topic438415-266-1.aspx
Nice 🙂
For an MS/hybrid version I altered the results of EXEC sp_helptext sp_msforeachdb to suit my own needs too. 😉
gsc_dba
September 14, 2011 at 10:11 am
Thank you all for your wonderful replies.
"RedGate Software" is Liscenced.
Any open Source TOOL?
September 14, 2011 at 10:15 am
waseem.shahzad 45937 (9/14/2011)
Thank you all for your wonderful replies."RedGate Software" is Liscenced.
Any open Source TOOL?
http://www.ssmstoolspack.com/Features?f=5
gsc_dba
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply