Loop through a list of databases and run SQL file(s)

Question

I have a number of databases on which I should run the same SQL file. The database names can be found in a table in another database. I was wondering if I can automate this somehow to avoid changing database manually and running the script. I'm trying to make following script working (in sqlcmd mode):

:On Error Exit
declare @statement nvarchar(MAX), @dbname nvarchar(255)

declare NameList cursor for
select database_name  from databases where type ='s'
OPEN NameList
FETCH NEXT FROM NameList 
INTO @dbname
WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @statement = N'USE '+@dbname+N'
  GO'

  EXEC dbo.sp_executesql @statement

  :r C:\temp\check_db.sql
  GO
  FETCH NEXT FROM NameList INTO @dbname
END
CLOSE NameList
DEALLOCATE NameList

The idea is to go through the list of databases and use USE <DB> statement to change the current database to one fetched from the list, then run the script from SQL file.

I'm getting error Incorrect syntax near <some point in SQL file>. which points a row in the SQL file thus I'm concluding that USE <DB> is not changing the current database for the SQL file.

"Incorrect syntax" means you are using the wrong database? I think you're right about using the wrong database, but I don't think that's what's causing the syntax error. — Aaron Bertrand, Nov 26 '13 at 15:16
@AaronBertrand the syntax error says that an object missing in the database where I run this script but which should be found in the database which name is stored in `@dbname`. — Azimuth, Nov 26 '13 at 15:53
Those are two different errors, then. An object not found error is not ever reported as an incorrect syntax error. — Aaron Bertrand, Nov 26 '13 at 15:54

Philip Kelley · Answer 1 · 2013-11-26T15:32:25.887

2

The problem is that you issue a USE <Database> command in the dynamic sql executed the sp_executeSQL. The new “database context” remains selected only for the duration of that session (as long as sp_executeSQL is running). So:

You start in your initial database (“A”)
You execute the dynamic SQL
While that’s running, database context is switched to the target datab ase (“B”)
The dynamic SQL finishes
The context reverts back to that of the calling session (“A”)

What you’d need to do is embed the USE statement in the SQL being performed, in check_db.sql. That, or have the SQLCMD set the database for you, if that can be done as a sqlcmd “inline” command.

edited Nov 26 '13 at 15:32

answered Nov 26 '13 at 15:23

Philip Kelley

39,426
11
57
92

@Aaron's right, I see no syntax error in what you've listed. That would be generated from the execution of your .sql file... and as that's not running on the database you want it to be running on, that may be where your syntax error is coming from. – Philip Kelley Nov 26 '13 at 15:24
Fatfingers strike again! Also, it looks like you can't change database context using inline sqlcmd Commands. – Philip Kelley Nov 26 '13 at 15:33
1

If the .sql file doesn't contain non-T-SQL things like `GO`, you could potentially load it into an `nvarchar(MAX)` variable and execute it with nested EXEC database..sp_executesql ... but that will be quite messy. I'd rather just run the .sql files directly from outside the database and without bothering to try to use SSMS for this - batch file calling sqlcmd, C#, Powershell, etc. etc. Much better suited for this sort of task. – Aaron Bertrand Nov 26 '13 at 15:35
@AaronBertrand Any suggestion then how I could do this in PowerShell for example? I really want to avoid opening the file in SMSS and run the script changing the current database manually one by one... – Azimuth Nov 26 '13 at 15:54
@Azimuth you should post a new question without the code above, add the powershell tag, and basically just ask "I want to run a .sql file against multiple databases." You should get an answer within minutes (you won't want the answer I would come up with). – Aaron Bertrand Nov 26 '13 at 15:55
@AaronBertrand will do :) Just strange for me that it's not possible to do this in SMSS... – Azimuth Nov 26 '13 at 15:56
@Azimuth it is possible, just not worth the effort. Why do you want to use SSMS for this task? I could mow my lawn with scissors, too, and even though it's possible, it is just not worth the effort. – Aaron Bertrand Nov 26 '13 at 15:58
@AaronBertrand I agree. I should have searched before posting this question. Searched `powershell run sql on multiple databases` on Google and got tons of examples... P.S. how do I remove this question? – Azimuth Nov 26 '13 at 16:00
@Azimuth don't rush to remove the question. Philip deserves the up-vote he got (you should up-vote him too), and you may get some more creative answers in the meantime, even if you do end up using a different solution. And it could be useful to other readers. – Aaron Bertrand Nov 26 '13 at 16:01
@AaronBertrand good point! Glad that I got a working solution with your help as well :) – Azimuth Nov 26 '13 at 16:05
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/41979/discussion-between-azimuth-and-aaron-bertrand) – Azimuth Nov 26 '13 at 16:17

Loop through a list of databases and run SQL file(s)

1 Answers1