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
– Aaron Bertrand, Commented Nov 26, 2013 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
– Azimuth, Commented Nov 26, 2013 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
– Aaron Bertrand, Commented Nov 26, 2013 at 15:54

Philip Kelley · Accepted Answer · 2013-11-26 15:32:25Z

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, 2013 at 15:32

answered Nov 26, 2013 at 15:23

Philip Kelley

40.5k11 gold badges62 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Philip Kelley Over a year ago

@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 Over a year ago

Fatfingers strike again! Also, it looks like you can't change database context using inline sqlcmd Commands.

Aaron Bertrand Over a year ago

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.

Azimuth Over a year ago

@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...

Aaron Bertrand Over a year ago

@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).

|

Collectives™ on Stack Overflow

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

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related