asked
How to Execute sql script in multiple database without use of tsql
I have one sql script which contains alter/create script of many tables/store procedures . I want to execute in multiple database in one server.I can not use tsql in script.
Why cant you use T-SQL in the script? This would be a candidate for the undocumented command sp_msforeachdb
@anthony . Because script have lots of line of store procedure and functions. so tsql is not easy for me
@anthony. i have so many database. so i can not execute one by one. Please tell me if any other solution. thanks for comment.
Only other way if you don't want to do it one by one is to write your own script/application which can connect to multiple databases and read in and execute your script file. Or put you hand in your pocket and pay for a product like ApexSQL Build which has the ability to execute scripts against multiple databases
You have some answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
If you know the DB's this needs running against, open the script in SSMS, and use the DB drop down list from the top left and select DB's one by one, pressing execute on the script each time you change the DB context. Or at the top of the script use the below snippet USE [DBNAME] GO And change the DBName for each DB you want to run it against. Or use SQLCMD and change the DB context at the connection string level
In addition to @Anthony.Green's suggestions, you could make use of SSMS's "Registered Server" facility. Open SSMS. Click on View menu and select "Registered Servers". Create a new group somewhere. Inside that group, right-click, select "New connection". Fill out the server authentication details, and go to the second tab, "Connection Properties". Change the database name. Once you've gone through all that pain, you can then right click on the group of servers, and open a new query window that will allow you to simultaneously run a query against all those databases at once. But, seriously, this is an incredibly tedious and error-prone way of dealing with the problem.
My take on it was that there are 10 DBs on 1 server so didnt go down the registered server route as the name suggests its a server not a db. How ever just tested creating 5 test DB and 5 registered servers pointing to the same server but different databases, then running a group query SELECT db_name() and low and behold it returns the individual DB names. Can't think of a reason behind using a registered server in this way at the moment, but yes it does do what the OP needs to do.
You can do this with PowerShell fairly easy. So in PowerShell you could do this a few different ways: 1. Mix of SMO and SQLPS using SMO to get list of databases and then iterate over the databases calling `Invoke-Sqlcmd` and just change the database context. 2. Pure .NET just using `System.Data.SqlClient`, pull in the content of the script and get list of databases to adjust the connection string. (A bit overkill to me). 3. If your script contains the `GO` terminator the .NET method will not handle it, you will need to use SMO's `ConnectionContext.ExecuteNonQuery()`. I blogged an example of using SMO for a task where I had to create a user and database role in 1700 databases, [you can find that here][1]. I would say of the list above using `Invoke-Sqlcmd` is probably your simplest method. So at a basic level, you could do something like: [cmdletbinding()] param($server,$scriptFile) Import-Module SQLPS $scriptToRun = Get-Conent $scriptFile -Raw $qDBNames = "SELECT name FROM sys.databases;" $databaseList = Invoke-SqlCmd -ServerInstance $server -Query $qDBNames | select -ExpandProperty name foreach ($d in $databaseList) { Invoke-SqlCmd -ServerInstance $server -Database $d -Query $scriptToRun } [1]:
https://meltondba.wordpress.com/2015/10/22/sql-server-fast-food/
21 People are following this question.
